Using Bookmarks to Fill in a Word Document from Excel
This page shows an example of how to create a mail merge from Word to Excel. It's quite a long example, but should be a great aid to helping understand how to link applications using VBA.
Files Needed
The files that you'll need to get this to work are as follows:
The macros are written in Excel 2010 and Word 2010, but should work in Office version 2007 also.
The Aim of the Exercise
Suppose that we have a list of people in an Excel worksheet:
Our aim is to create a "letter" in Word addressed to each of these people.
If we succeed, the end result will be 3 documents saved in Word - the one for Tinky Winky will look like this, for example:
The form filled in for Tinky Winky, the second of the 3 people we're writing to.
To do this we'll loop over the people in the list in Excel, creating a document in Word for each.
Preparing the Word Document
We now need to create our Word template form and add bookmarks to it, so that we know where to insert the various fields. The template form begins life as a simple table:
The first name, last name and company name will appear in the table.
The next thing to do is to make sure that we can see any bookmarks that we add.
A bookmark in Word is a flag in a document which you can jump to - like an insertion point.
To view bookmarks, follow these steps:
You can now insert bookmarks - we'll create 3, called FirstName, LastName and Company. For each bookmark, select the following option and fill in the dialog box which appears:
The ribbon option to insert a bookmark
After creating 3 bookmarks, your document should now look like this:
You can see where the 3 bookmarks have been inserted.
After saving this document, you now need to know how to go to a bookmark in Word from within your Excel code.
Recording a Macro to go to a Bookmark in Word
If you happen to know Word Visual Basic inside-out, you can ignore this step!
Firstly, start recording a macro (if you're not sure what recording a macro means or how to do it, see my earlier tutorial on recording macros in Excel).
Click on this tool to start recording a macro.
While recording (it doesn't really matter what you called your macro), choose to find a bookmark:
Choose which bookmark you want to go to:
Choose to go to a bookmark, then choose one from the list (it doesn't matter which).
Now stop recording:
Click on this tool to stop recording your macro.
If you press ALT + F11 to go into Visual Basic, you'll find your recorded macro in the Normal template:
Word by default puts recorded macros into a module called NewMacros.
You can now see your macro, and copy the only line you want from it:
Selection.GoTo What:=wdGoToBookmark, _
Name:="LastName"
You can now close down Word, and write your Excel macro!
Writing the Excel Macro
The final stage is to open your workbook in Excel, and write your macro:
A reminder of what the Excel workbook looks like.
To write your macro, go into VBA and create a reference to Word 2010 as described earlier in this blog. You can then create the macro - here's what it could look like:
Option Explicit
Const FilePath As String = "C:\ajb files\"
Dim wd As New Word.Application
Dim PersonCell As Range
Sub CreateWordDocuments()
Dim doc As Word.Document
wd.Visible = True
Dim PersonRange As Range
Range("A1").Select
Set PersonRange = Range( _
ActiveCell, _
ActiveCell.End(xlDown))
For Each PersonCell In PersonRange
Set doc = wd.Documents.Open(FilePath & "Word form.docx")
CopyCell "FirstName", 1
CopyCell "LastName", 2
CopyCell "Company", 3
doc.SaveAs2 FilePath & "person " & PersonCell.Value & ".docx"
doc.Close
Next PersonCell
wd.Quit
MsgBox "Created files in " & FilePath & "!"
End Sub
Sub CopyCell(BookMarkName As String, ColumnOffset As Integer)
wd.Selection.GoTo What:=wdGoToBookmark, Name:=BookMarkName
wd.Selection.TypeText PersonCell.Offset(0, ColumnOffset).Value
End Sub
When you run this macro, it should:
Create a new copy of Word in memory, and make it visible; then
For each person, create a new document, write in the person's name and company at the correct locations, then save and close it.
The results? Three documents on your hard disk:
What you should see after running your macro - each document contains a filled-in form for a different person.
And that's how you can fill in a Word document from within Excel!
If you'd like to go on one of our VBA courses and have a real live owl explain this to you, consider booking on our Advanced VBA course - wherever you are in the world, for the online version.
