Sub to document a project in Word

[KentBurel]

This is not a question but a contribution. I have been writing VBA code to automate a business process. I needed a way to document all the code I've written. So I wrote this sub that reads each codemodule in the project and writes it to a word document.

Option Explicit
Sub DocumentVBA()

' First step is to setup to word environment

    Dim wdApp       As Word.Application

    Dim VBProj      As VBIDE.VBProject
    Dim VBComp      As VBIDE.VBComponent
    Dim CodeMod     As VBIDE.CodeModule
    Dim codify_time As String
    Dim filename    As String
   
    Set VBProj = Workbooks("LogicAndAccuracyTesting.xlsm").VBProject
    Set wdApp = New Word.Application
   
    With wdApp
        .Visible = True
        .Activate
       
        ' Create a new document
        .Documents.Add
       
        ' Adjust Heading 1 style so that each module starts on a new page
        With ActiveDocument.Styles("Heading 1").ParagraphFormat
            .SpaceAfter = 12
            .PageBreakBefore = True
        End With
       
        ' Adjust Normal style so that everything is single spaced.
        With ActiveDocument.Styles("Normal").ParagraphFormat
            .SpaceBefore = 0
            .SpaceAfter = 0
            .LineSpacingRule = wdLineSpaceSingle
        End With
       
       
    End With
       
' Now access the vba components

    For Each VBComp In VBProj.VBComponents
       
        If VBComp.Type = vbext_ct_StdModule Then
       
    ' Write out the name of the component with the style of Header 1
          
            With wdApp.Selection
                .Style = ActiveDocument.Styles("Heading 1")
                .TypeText Text:="VB Code Module for " & VBComp.Name
                .TypeParagraph
            End With
           
            Set CodeMod = VBComp.CodeModule
           
            If CodeMod.CountOfLines <> 0 Then
                With wdApp.Selection
                    .Style = ActiveDocument.Styles("Normal")
                    .TypeText Text:=CodeMod.Lines(1, CodeMod.CountOfLines)
                    .TypeParagraph
                End With
            End If
        End If
       
    Next VBComp
   
    ' Now set landscape mode with narrow margins
    With wdApp.Selection.PageSetup
        .Orientation = wdOrientLandscape
        .TopMargin = InchesToPoints(0.5)
        .BottomMargin = InchesToPoints(0.5)
        .LeftMargin = InchesToPoints(0.5)
        .RightMargin = InchesToPoints(0.5)
        .HeaderDistance = InchesToPoints(0.5)
        .FooterDistance = InchesToPoints(0.5)
        .PageWidth = InchesToPoints(11)
        .PageHeight = InchesToPoints(8.5)
    End With
   
' Time to save and close the word document
    codify_time = Format(Now, "yyyymmdd_hhmmss")
    filename = ThisWorkbook.Path & "\Excel VBA Code." & codify_time & ".docx"
   
    With wdApp
        .ActiveDocument.SaveAs2 (filename)
        .ActiveDocument.Close
        .Quit
    End With
   
    Set wdApp = Nothing ' We are done with word

End Sub

 

[rlv01]

For your posted code to compile & run, it needs the user to have certain VBA project references checked (Word Obj Library, VBA extensibilty). You might want to include those requirments as code comments.

 

[KentBurel]

For your posted code to compile & run, it needs the user to have certain VBA project references checked (Word Obj Library, VBA extensibilty). You might want to include those requirements as code comments.

I will. Thanks for the feedback.

 

[KentBurel]

This is not a question but a contribution. I have been writing VBA code to automate a business process. I needed a way to document all the code I've written. So I wrote this sub that reads each codemodule in the project and writes it to a word document.

Option Explicit
Sub DocumentVBA()

' ----------------------------------------------------------------------------------------------------------------------------------------------------------
' In order to compile and run this code, you must first extend the VB Editor by including a couple of libraries in the reference list.
' Fire up Excel and go into VBA.  Now Select Tools -> References.  Make sure that you have a check next to these two libraries:
' Microsoft Visual Basic for Applications Extensibility 5.3 and
' Microsoft Word 16.0 Object Library
' These are the two I use on my Microsoft 365 system.  You may have lower level versions on your system.  Earlier versions
' should work without problem.  These references are remembered in the workbook so they do not persist from one
' workbook to another. 
'
' This code does not work when the workbook you are reporting is open from a OneDrive directory.  The path name to the saved Word
' file will be invalid in that case.
' ---------------------------------------------------------------------------------------------------------------------------------------------------------

' First step is to setup to word environment

    Dim wdApp       As Word.Application

    Dim VBProj      As VBIDE.VBProject
    Dim VBComp      As VBIDE.VBComponent
    Dim CodeMod     As VBIDE.CodeModule
    Dim codify_time As String
    Dim filename    As String
  
    Set VBProj = ThisWorkBook.VBProject
    Set wdApp = New Word.Application
  
    With wdApp
        .Visible = True
        .Activate
      
        ' Create a new document
        .Documents.Add
      
        ' Adjust Heading 1 style so that each module starts on a new page
        With ActiveDocument.Styles("Heading 1").ParagraphFormat
            .SpaceAfter = 12
            .PageBreakBefore = True
        End With
      
        ' Adjust Normal style so that everything is single spaced.
        With ActiveDocument.Styles("Normal").ParagraphFormat
            .SpaceBefore = 0
            .SpaceAfter = 0
            .LineSpacingRule = wdLineSpaceSingle
        End With
      
      
    End With
      
' Now access the vba components

    For Each VBComp In VBProj.VBComponents
      
        If VBComp.Type = vbext_ct_StdModule Then
      
    ' Write out the name of the component with the style of Header 1
         
            With wdApp.Selection
                .Style = ActiveDocument.Styles("Heading 1")
                .TypeText Text:="VB Code Module for " & VBComp.Name
                .TypeParagraph
            End With
          
            Set CodeMod = VBComp.CodeModule
          
            If CodeMod.CountOfLines <> 0 Then
                With wdApp.Selection
                    .Style = ActiveDocument.Styles("Normal")
                    .TypeText Text:=CodeMod.Lines(1, CodeMod.CountOfLines)
                    .TypeParagraph
                End With
            End If
        End If
      
    Next VBComp
  
    ' Now set landscape mode with narrow margins
    With wdApp.Selection.PageSetup
        .Orientation = wdOrientLandscape
        .TopMargin = InchesToPoints(0.5)
        .BottomMargin = InchesToPoints(0.5)
        .LeftMargin = InchesToPoints(0.5)
        .RightMargin = InchesToPoints(0.5)
        .HeaderDistance = InchesToPoints(0.5)
        .FooterDistance = InchesToPoints(0.5)
        .PageWidth = InchesToPoints(11)
        .PageHeight = InchesToPoints(8.5)
    End With
  
' Time to save and close the word document
    codify_time = Format(Now, "yyyymmdd_hhmmss")
    filename = ThisWorkbook.Path & "\Excel VBA Code." & codify_time & ".docx"
  
    With wdApp
        .ActiveDocument.SaveAs2 (filename)
        .ActiveDocument.Close
        .Quit
    End With
  
    Set wdApp = Nothing ' We are done with word

End Sub

 

[KentBurel]

Now that I have the basic functionality of this macro running I want to enhance it to make a complete Word document. So early in the macro I want to build a title page. I fired up word and used it's macro recorder while I added the title page. The code looks like this:

Sub Macro1()
    Application.Templates( _
        "C:\Users\mkent\AppData\Roaming\Microsoft\Document Building Blocks\1033\16\Built-In Building Blocks.dotx" _
        ).BuildingBlockEntries("Facet").Insert Where:=Selection.Range, RichText:= _
        True
    ActiveDocument.Shapes.Range(Array("Text Box 154")).Select
    Selection.TypeText Text:="Kent's main title" & vbCrLf & "Kent's sub title"
    ActiveDocument.Shapes.Range(Array("Text Box 153")).Select
    Selection.TypeText Text:="This is the abstract of the document"
    ActiveDocument.Shapes.Range(Array("Text Box 152")).Select
    Selection.TypeText Text:="mkentburel@gmail.com"
End Sub

I can run this code in word and sure enough a title page is added to the document. There are two changes that need to be made to get this to run in Excel.

The first is that the first line of the code, the Application.Templates line gives a total path to the Built-In Building Blocks.dotx file. The problem is that this path contains my user id of mkent. So I modified this line to use the environ("appdata") function to get the first part of the path and then concatenate it with the remainder of the path so that it will work when it is run by a different user.

The second required change is to change the Application object to point to the Word application rather than the Excel application. So now my macro looks like this:

Option Explicit
Sub DocumentVBA()

' ----------------------------------------------------------------------------------------------------------------------------------------------------------
' In order to compile and run this code, you must first extend the VB Editor by including a couple of libraries in the reference list.
' Fire up Excel and go into VBA.  Now Select Tools -> References.  Make sure that you have a check next to these two libraries:
' Microsoft Visual Basic for Applications Extensibility 5.3 and
' Microsoft Word 16.0 Object Library
' These are the two I use on my Microsoft 365 system.  You may have lower level versions on your system.  Earlier versions
' should work without problem.  These references are remembered in the workbook so they do not persist from one
' workbook to another.
'
' This code does not work when the workbook you are reporting is open from a OneDrive directory.  The path name to the saved Word
' file will be invalid in that case.
' ---------------------------------------------------------------------------------------------------------------------------------------------------------

' First step is to setup to word environment

    Dim wdApp       As Word.Application

    Dim VBProj      As VBIDE.VBProject
    Dim VBComp      As VBIDE.VBComponent
    Dim CodeMod     As VBIDE.CodeModule
    Dim codify_time As String
    Dim filename    As String
    Dim template    As String
   
    Set VBProj = ThisWorkbook.VBProject
    Set wdApp = New Word.Application
   
    With wdApp
        .Visible = True
        .Activate
       
        ' Create a new document
        .Documents.Add
       
        ' Adjust Heading 1 style so that each module starts on a new page
        With ActiveDocument.Styles("Heading 1").ParagraphFormat
            .SpaceAfter = 12
            .PageBreakBefore = True
        End With
       
        ' Adjust Normal style so that everything is single spaced.
        With ActiveDocument.Styles("Normal").ParagraphFormat
            .SpaceBefore = 0
            .SpaceAfter = 0
            .LineSpacingRule = wdLineSpaceSingle
        End With
       
'
' This section inserts the title page for the document
'
        wdApp.Templates(Environ("appdata") & _
            "\Microsoft\Document Building Blocks\1033\16\Built-In Building Blocks.dotx") _
            .BuildingBlockEntries("Facet").Insert Where:=Selection.Range, RichText:=True
        .ActiveDocument.Shapes.Range(Array("Text Box 154")).Select
        .Selection.TypeText Text:="Kent's Main title" & ChrW(11) & "Kent's sub title"
        .ActiveDocument.Shapes.Range(Array("Text Box 153")).Select
        .Selection.TypeText Text:="This is the main abstract of the document"
        .ActiveDocument.Shapes.Range(Array("Text Box 152")).Select
        .Selection.TypeText Text:="mkentburel@gmail.com"
       
        ' Now set landscape mode with narrow margins
        With .Selection.PageSetup
            .Orientation = wdOrientLandscape
            .TopMargin = InchesToPoints(0.5)
            .BottomMargin = InchesToPoints(0.5)
            .LeftMargin = InchesToPoints(0.5)
            .RightMargin = InchesToPoints(0.5)
            .HeaderDistance = InchesToPoints(0.5)
            .FooterDistance = InchesToPoints(0.5)
            .PageWidth = InchesToPoints(11)
            .PageHeight = InchesToPoints(8.5)
        End With
       
'
' This section turns on the page numbers
'
'
        If .ActiveWindow.View.SplitSpecial <> wdPaneNone Then
            .ActiveWindow.Panes(2).Close
        End If
        If .ActiveWindow.ActivePane.View.Type = wdNormalView Or .ActiveWindow. _
            ActivePane.View.Type = wdOutlineView Then
            .ActiveWindow.ActivePane.View.Type = wdPrintView
        End If
        .ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageFooter
        .Templates( _
            Environ("appdata") & "\Microsoft\Document Building Blocks\1033\16\Built-In Building Blocks.dotx" _
            ).BuildingBlockEntries("Plain Number 3").Insert Where:=Selection.Range, _
            RichText:=True
        .ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument
               
    End With
       
' Now access the vba components

    For Each VBComp In VBProj.VBComponents
       
    ' Write out the name of the component with the style of Header 1
          
            With wdApp.Selection
                .Style = ActiveDocument.Styles("Heading 1")
                .TypeText Text:="VB Code Module for " & VBComp.Name
                .TypeParagraph
            End With
           
            Set CodeMod = VBComp.CodeModule
           
            If CodeMod.CountOfLines <> 0 Then
                With wdApp.Selection
                    .Style = ActiveDocument.Styles("Normal")
                    ' the next line writes all the lines as a single word paragraph
                    .TypeText Text:=Replace(CodeMod.Lines(1, CodeMod.CountOfLines), vbCrLf, Chr(11))
                    .TypeParagraph
                End With
            End If
       
    Next VBComp
   
' Time to save and close the word document
    codify_time = Format(Now, "yyyymmdd_hhmmss")
    filename = ThisWorkbook.Path & "\Excel VBA Code." & codify_time & ".docx"
   
    With wdApp
        .ActiveDocument.SaveAs2 (filename)
        .ActiveDocument.Close
        .Quit
    End With
   
    Set wdApp = Nothing ' We are done with word

End Sub

When I run it, I make it to the wdApp.Templates line with the error message given:

What am I missing?