Microsoft Office Forums - View Single Post - Extract VBA code to save in Word document

gmayor · 01-20-2015, 11:38 PM

How about the following? This will put all the code from the template in which the macro is run into a new document, each module to a separate section. You can change 'ThisDocument' as required.

You will have to set a reference to Microsoft Visual Basic For Applications Extensibility 5.3

For more information, see http://www.cpearson.com/excel/vbe.aspx

Code:

Option Explicit

Sub CopyVBA()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim oDoc As Document
Dim oRng As Range
    Set oDoc = Documents.Add
    Set VBProj = ThisDocument.VBProject
    For Each VBComp In VBProj.VBComponents
        Set oRng = oDoc.Range
        With oRng
            .Collapse 0
            .Text = VBComp.name & vbCr & vbCr
            .Paragraphs(1).Range.Style = "Heading 1"
            .Collapse 0
            .Text = ReadModule(VBComp.name)
            .Style = "Normal"
            .Collapse 0
            .InsertBreak wdSectionBreakNextPage
        End With
    Next VBComp
lbl_Exit:
    Set oDoc = Nothing
    Set oRng = Nothing
    Set VBProj = Nothing
    Set VBComp = Nothing
    Exit Sub
End Sub


Function ReadModule(strModule) As String
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim oDoc As Document
    Set oDoc = ThisDocument
    Set VBProj = oDoc.VBProject
    Set VBComp = VBProj.VBComponents(strModule)
    ReadModule = VBComp.CodeModule.Lines(1, VBComp.CodeModule.CountOfLines)
lbl_Exit:
    Set oDoc = Nothing
    Set VBProj = Nothing
    Set VBComp = Nothing
    Exit Function
End Function