Extract VBA code to save in Word document

[Dave T]

Hello All,



This is my first post on this site...

Currently I am using Word 2007 and I keep all of my macros in an add-in.
I know that you can export modules as .bas files but I am wondering if there is any macro or third party product that can extract ALL of the VBA code from all of my modules, forms, etc. and export it as text to a Word document.

Currently I keep all of my useful macros in a Word document that is formatted using code from the VB Helper site called Use VBA macros to format text in Word to look like code (http://www.vb-helper.com/howto_format_code_in_word.html).

I prefer to keep the macros I use the most in a Word document so that I can print it and refer to it without sitting in front of a computer.

An agricultural approach could be is when I am within Visual Basic I could go to File > Print > Current Project using Adobe PDF as the output and then copying the text from this to paste in my Word document.

Any other suggestions would be appreciated.

Regards,
Dave T

[gmayor]

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

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

[Dave T]

Hello Graham,

Sorry about the delay in replying to you, but school holidays were about to finish and I needed to take the kids camping.

Your macro is fantastic and does more that I expected...
The heading style for the Module name is an excellent touch.

To get your macro to run I also needed to 'Check the Trust access to the VBA project object model."

Thanks again for your help.

Regards,
Dave T

[macropod]

FWIW, I have a paragraph Style named 'Macro' that I've defined for macro code. Basically, it uses a blue Courier-New font. Then, by replacing all the paragraph breaks you get when pasting macros into Word with line breaks, each macro remains a single paragraph. That makes it easier for copying & pasting back into the VBE. Graham's macro could be modified to do something similar and to apply a Style other than 'Normal'.

[Dave T]

Hello Paul,

Thanks for the suggestion.

Rather than having to create a specific 'macro' style in the destination document I have already modified Graham's code so that the output is in 8pt Courier New.

I have always copied macros from various source and pasted them as 'unformatted text' into my Word document library of useful macros and then applied the macro styles as defined in my first post.

I had not thought of replacing the paragraph breaks with line breaks, so I will have a look at that. Thanks for the suggestion.

Regards,
Dave T