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

Instead of coding all this content, you should learn to use a Word template that has all the boilerplate content in-situ. It's then just a simple matter of referencing that template via your existing. Documents.Add code. — macropod
Well, we need this to spit out a Word doc on a lot of different users' computers, who use our front end database. And some of them have their own .dot files that I don't want to mess with. But yes, if this was just on my computer, I wouldn't bother with this. — Emily Beth
That doesn't change anything. You can use your own templates without affecting those of your client. Word 101. — macropod
OK thanks, I'll look into that also. So I would just have some other template on a network drive somewhere and reference that new location in the Document.Add statement? I'll say that while all of these employees have access to the same SQL Server, I'm not sure they all have access to the same network drives. So not entirely sure that will work if that is the idea. — Emily Beth
Since this is all being done with code, I'd be inclined to store the Word template in the same folder as your database. Hence: Set doc = .Documents.Add(CurrentProject.Path & "\MyTemplate.Dotx"). Your users will then be able to save the new document to whatever drives/folders they have write permissions for. — macropod

Timothy Rylatt Timothy Rylatt · Accepted Answer · 2021-05-13T19:32:13

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

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

2 Answers