Use Access VBA to add formatted/justified header and footer to Word doc

Question

Is there a way to add a header and footer to a Word document using VBA, as described below?

The header should be a combination of:

• "New Items Report" in bold 16 pt font that is left-justified, and
• The current date and time in non-bold, 12 pt font that is right-justified. (I defined a myDateTime variable in the code below to use for the date/time string.)

The footer should be a centered "Page X of Y" (where X and Y are fields for the page # and # of pages, respectively).

I know well how to do this manually in Word, but despite many Google searches can't quite crack this (or really figure out where to start) using VBA. Below is Access VBA code that I have currently to create the document.

Appreciate any solutions or pointers.

' declare vars and set up

    Dim objWord As Word.Application
    Dim doc As Word.Document
    Dim WordHeaderFooter As HeaderFooter
    Dim myDateTime As String

    ' variable to be used as Date/Time in header
    myDateTime = Format(Now(), "Long Date") & " " & Format(Now(), "Medium Time")
    
    Set objWord = CreateObject("Word.Application")

' create doc and insert sample text

    With objWord
        
        .Visible = True
    
        Set doc = .Documents.Add
        doc.SaveAs CurrentProject.Path & "\TestDoc.doc"
        
    End With
                  
    With objWord.Selection
          
        .Font.Name = "Calibri (Body)"
        .Font.Size = 12
        .TypeText "Here is an example line of text." 
      
    End With
    
    doc.Save
    doc.Activate

Answer 1

Setting headers in Word to have text left/right justified can be tricky as, unlike Excel, there is no left/center/right headers - it's one header.

In the below code I've used Tabs to 'justify' the header to New Items Report is on the left and the date is on the right.

Option Explicit

Sub AddWordHeaderAndFooter()
    ' declare vars and set up

    Dim objWord As Word.Application
    Dim doc As Word.Document
    Dim rngHeader As Word.Range
    Dim rngFooter As Word.Range
    Dim myDateTime As String

    ' variable to be used as Date/Time in header
    myDateTime = Format(Now(), "Long Date") & " " & Format(Now(), "Medium Time")
    
    Set objWord = CreateObject("Word.Application")

    ' create doc and insert sample text

    With objWord
        
        .Visible = True
    
        Set doc = .Documents.Add
        doc.SaveAs CurrentProject.Path & "\TestDoc.doc"
        
    End With
                  
    With objWord.Selection
          
        .Font.Name = "Calibri (Body)"
        .Font.Size = 12
        .TypeText "Here is an example line of text."
      
    End With
    
    Set rngHeader = doc.Sections(1).Headers(wdHeaderFooterPrimary).Range
    
    With rngHeader
        With .Font
            .Bold = True
            .Size = 16
        End With
        .Text = "New Items Report"

        .Collapse wdCollapseEnd
        .MoveEnd wdCharacter, 0
        .InsertAfter vbTab
        .InsertAfter vbTab
        .InsertAfter myDateTime
 
        With .Font
            .Bold = False
            .Size = 12
        End With
    End With
    
    Set rngFooter = doc.Sections(1).Footers(wdHeaderFooterPrimary).Range
    
    With rngFooter

        .InsertAfter vbTab & "Page "
        .Fields.Add .Characters.Last, wdFieldEmpty, "PAGE", False
        .InsertAfter " of "
        .Fields.Add .Characters.Last, wdFieldEmpty, "NUMPAGES", False

    End With
            
    doc.Save
    doc.Activate
    
End Sub

Answer 2

If you use alignment tabs setting headers in Word to have text left/right justified isn't tricky at all. You are more likely to encounter problems if you use normal tabs.

By default the Header and Footer styles in Word have tab stops that correspond to the default margins. If your page layout is different to the default the tab stops will be in the wrong positions.

This can be avoided by using alignment tabs, which IIRC were introduced in Word 2010. These enable you to set tabs that align either to the paragraph indents or the page margins.

Sub AddWordHeaderAndFooter()
   ' declare vars and set up

   Dim objWord As Word.Application
   Dim doc As Word.Document
   Dim rngHeader As Word.Range
   Dim rngFooter As Word.Range
   Dim myDateTime As String

   ' variable to be used as Date/Time in header
   myDateTime = Format(Now(), "Long Date") & " " & Format(Now(), "Medium Time")
    
   Set objWord = CreateObject("Word.Application")

   ' create doc and insert sample text

   With objWord
        
      .Visible = True
    
      Set doc = Documents.Add
      doc.SaveAs CurrentProject.Path & "\TestDoc.doc"
        
   End With
                  
   With doc.Content
          
      .Font.Name = "Calibri (Body)"
      .Font.Size = 12
      .Text = "Here is an example line of text."
      
   End With
    
   Set rngHeader = doc.Sections(1).Headers(wdHeaderFooterPrimary).Range
    
   With rngHeader
      With .Font
         .Bold = True
         .Size = 16
      End With
      .Text = "New Items Report"

      .Collapse wdCollapseEnd
      .InsertAlignmentTab Alignment:=wdRight, RelativeTo:=wdMargin
      With .Characters.Last
         With .Font
            .Bold = False
            .Size = 12
         End With
         .InsertAfter myDateTime
      End With
   End With
    
   Set rngFooter = doc.Sections(1).Footers(wdHeaderFooterPrimary).Range
    
   With rngFooter

      .InsertAlignmentTab Alignment:=wdCenter, RelativeTo:=wdMargin
      .InsertAfter "Page "
      .Fields.Add .Characters.Last, wdFieldEmpty, "PAGE", False
      .InsertAfter " of "
      .Fields.Add .Characters.Last, wdFieldEmpty, "NUMPAGES", False

   End With
            
   doc.Save
   doc.Activate
    
End Sub