Using VBA to insert header and footer images in Word document Letterhead Templates

A company that was rebranding approached me with the desire to create one universal letterhead template file that would work for all office locations.  When the file opened, they wanted a prompt to ask the end user which office they would like to create a letterhead document.  The end user selection then was to be used to populate specific office information such as address and phone numbers in the header and footer areas.  This task was first solved by using Visual Basic (VBA) inside Microsoft Word to create a user form with controls that are mapped to specific bookmarks in the template.  But this working solution ran into issues with the client’s desire to have direct control over fine-tuning the exact placement of the text and its font characteristics such as size and boldness.  They didn’t want to have to modify the VBA code whenever they wanted tweaking done.  Thus, the solution was changed to using VBA to insert images of the header and footer information instead of using VBA to place textual information.  This way modification of the template could be done in-house by the graphics department.  Simply by having graphics provide refreshed image files the template would be updated through the dynamic use of the user form pulling in the new images.

The approach I used can be broken down into these major steps:

1. Create your header and image files and place them in a folder called Images.
2. Create a source Word document containing a table of the office location names and the names of the header and footer image files.
3. Set bookmarks in the template file to indicate where the images are to be placed in the document.
4. Code a user form box to pull the office information from the source table and display that in a simple window as the template file opens.
5. Write VBA code to take the input from the user form box and place the appropriate image files at the correct bookmarks.
6. Scale the images to the appropriate magnification level by using scaling commands in the VBA code.

Each of these steps is detailed below.

Step 1:  Create header and footer image files and place them in a folder called Images

Use a graphic program to create your image and footer image files.  I made my images 8.5” inches wide.  As for the length, I made them as tall as the top margin setting for the header files and as tall as the bottom margin for the footer files.  Place all these images in a folder called Images.  Store this image folder in the same folder as the sourceaddresses.docx file created in Step 2 and the letterhead template file created in Step 3.

Step 2: Create a source Word document containing a table of office location names and the names of the header and footer images

Create a new word document called SourceAddresses.docx.  Insert a table containing office location names and the names of the header and footer images.  Make sure the table has a header row indicating the content of each column.  The table information will be read by the VBA.  For the code I have shared in this post, have column 3 and column 4 contain the names of header images and footer images, respectively. Any other content in the document, besides the table area, will be skipped so if you want to type a title for the table and other notes below, it will not impact the VBA code being able to read the table of information.

Step 3: Set bookmarks in the template file to designate where the images are to be placed

3a. Setup Word options so that you can see the bookmarks you will set:

By default Word will keep bookmarks hidden.  Do these steps, so

you can see where they are set:

1. Open your template file and save it as a Macro-Enabled template (*.dotx).
2. Click on the File tab and then choose Options.
3. Go to the Advanced category on the left hand side.
4. In the Show Document Content section, put a checkmark in the Show Bookmarks box.  Now bookmarks inserted in the document will be seen by an I-beam symbol.

3b. Insert a bookmark called BkmHead in the very upper left corner of header by these steps:

1. Double click within the top 0.5” of the document to get into the Header area
2. Move your cursor all the way to edge of the piece of paper by moving your left indent markers all the way to the edge of the paper.  If you have standard 1” margins then you can also do this by entering -1.0 in the left indent box on the Layout tab.
3. In the Header from top box enter a value of 0”.  Your cursor should now be in the very upper left corner of the document.
4. Click on the Insert tab and then on Bookmark.  Add a bookmark with the name BkmHead.  You should now see the header bookmark at position A

3c. Insert a bookmark called BkmFoot in the very lower left corner of footer by these steps:

1. Get your cursor in the Footer area.
2. Move your cursor all the way to edge of the piece of paper by moving your left indent markers all the way to the edge of the paper.  If you have standard 1” margins, then you can also do this by entering -1.0 in the left indent box on the Layout tab.
3. In the Footer from bottom box enter a value of 0” Your cursor should now be in the very bottom left corner of the document.
4. Click on the Insert tab and then on Bookmark.  Add a bookmark with the name BkmFoot.  You should now see the footer bookmark at position B.

Step 4:  Create a VBA user form to read the office information and display it to end user

A great guide to help me get started was found in a similar project on tech republic

4a:  Create the userform

1. Open VBA editor by pressing [Alt] + [F11]
2. Then go the Insert menu and choose UserForm.  The toolbox should automatically be displayed.  If you do not see it then choose Toolbox from the View menu.
3. Add  a list box and a command box control to the userform and make them the desired size.
4. Name the list box and the command box by using the name box in Properties window.
5. Use the Caption box in Properties window to give the user form and the command button user friendly verbiage.  For example, for the user form  enter a Caption of something like “Select your office” and for the Command button enter a caption of “OK”.

4b:  Add the code to have the userform read the table of addresses display it in the userform:

Double-click the userform to return to its module and then enter the following code which stores the table information in an array and then populates the array information in the userform:

Private Sub UserForm_Initialize()
Dim arrData() As String
Dim sourcedoc As Document
Dim sourcename As String
Dim I As Integer
Dim j As Integer
Dim myitem As Range
Dim m As Long
Dim n As Long
  Application.ScreenUpdating = False
  
  'Code opens the file called Sourceaddresses.docx and reads the contents
  'Note:  The file SourceAddresses.docx must be located in the same files as this template file.  Never move or rename it.
   Set sourcedoc = Documents.Open(FileName:=ThisDocument.Path & "\SourceAddresses.docx", Visible:=False)
    
  'Get the number of list members (i.e., table rows - 1 if header row is used)
  I = sourcedoc.Tables(1).Rows.Count - 1
  'Get the number of list member attritbutes (i.e., table columns)
  j = sourcedoc.Tables(1).Columns.Count
  
  'Set the number of columns in the Listbox
  ListBox1.ColumnCount = j
  
  'Load list members into an array
  ReDim arrData(I - 1, j - 1)
  For n = 0 To j - 1
    For m = 0 To I - 1
      Set myitem = sourcedoc.Tables(1).Cell(m + 2, n + 1).Range
      myitem.End = myitem.End - 1
      arrData(m, n) = myitem.Text
    Next m
  Next n
  
  'Use the .List property to populate the listbox with the array data
  ListBox1.List = arrData
  'Close the source file
  sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
lbl_Exit:
  Exit Sub
End Sub

4c:  Add the code to run the userform automatically:

Open ThisDocument module from the Project Explorer window.   Enter and save these short lines of code.  This will make the userform appear upon the opening of the template file.

Sub AutoNew() 
UserForm1.Show 
End Sub

Once you have added this code, save and close the template file.  Then double-click on the template file to see if the userform works.  The userform should look something like this.

Step 5:  Write VBA code to place the images at the appropriate bookmarks

Open VBA editor by pressing [Alt] + [F11].   Go to Userform1 and  add the lines of code below underneath the lines of code that you added in Step 4b above.

This code places the image files at the bookmarks set in the Header and the Footer. The very bottom section of the code is also necessary to change to print layout view (default view) and to force the cursor to be located in main document.  These additional steps are necessary because insertion of the bookmarks is in the Header and Footer area leaves the cursor in that location for the end user.

Private Sub cmdOK_Click()

' Dim header_image As String
' Dim footer_image As String

Dim header_image As String
Dim footer_image As String
'
'
' 'Load the option selected in the listbox and delete return characters unless they are in the address field
If Not IsNull(ListBox1) Then

header_image = Me.ListBox1.Column(2)
header_image = Replace(header_image, Chr(13), "")
footer_image = Me.ListBox1.Column(3)
footer_image = Replace(footer_image, Chr(13), "")

Dim BmkNm As String

' PLACE HEADER IMAGE at the bookmark called "BkmHead" and scale it to make it the desired size
BmkNm = "BkmHead"
With ActiveDocument
If .Bookmarks.Exists(BmkNm) Then
Dim WrdPic As Word.InlineShape
Set WrdPic = .Bookmarks(BmkNm).Range.InlineShapes.AddPicture(ThisDocument.Path & "\Images\" & header_image, False, True)

With WrdPic
.ScaleHeight = 84
.ScaleWidth = 84
End With
End If
End With

' PLACE FOOTER IMAGE at the bookmark called "BkmFoot" and scale it to make it the desired size
BmkNm = "BkmFoot"
With ActiveDocument
If .Bookmarks.Exists(BmkNm) Then
Set WrdPic = .Bookmarks(BmkNm).Range.InlineShapes.AddPicture(ThisDocument.Path & "\Images\" & footer_image, False, True)

With WrdPic
.ScaleHeight = 84
.ScaleWidth = 84
End With
End If
End With
'
'
'
'CHANGE TO PRINT LAYOUT VIEW & FORCE CURSOR TO BE IN MAIN DOCUMENT
ActiveDocument.ActiveWindow.View = wdPrintView
ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument
If ActiveWindow.View.SplitSpecial = wdPaneNone Then
ActiveWindow.ActivePane.View.Type = wdPrintView
Else
ActiveWindow.View.Type = wdPrintView
End If

Unload Me

Selection.HomeKey Unit:=wdStory
End If

End Sub

Step 6: Scale the images to the appropriate magnification level by using scaling commands in the VBA code.

Before you do this final step, open the template file to see if it successfully places the image files in the header and footer.  The code should work well except you will probably notice that the images are too small.

So the next step is to adjust the .ScaleHeight and .ScaleWidth properties to appropriate values so that the image will have the right dimensions.  The easiest way to do this is by reading the dimension of the image placed into Word in inches.   Then think about the desired width you would like your image to be in Word.   Adjust the..ScaleHeight and .ScaleWidth by the ratio of the desired width over the current width.  When working with my images I created them to span the width of the document so I wanted them to be 8.5” wide. I adjusted ScaleWidth and ScaleHeight to values that got me a width of 8.5″.

Sample Files to Download:

Feel free to download my set of sample files.  After extracting them, open the file called LetterheadTemplatefor AllOfficeLocations.dotm.   Images will placed in the both the Header and Footer areas per the end user’s request.  If you chose Location C, the document generated will look like this.

If you find this post helpful, be sure to leave a comment.

Thanks.