Referencing Word and other applications in Excel VBA
Part four of a four-part series of blogs

Even if you're writing Visual Basic macros within Excel, there's nothing to stop you inserting text into Word documents, manipulating PowerPoint slides or sending Outlook emails. This blog explains how to reference other applications, and write macros in Excel to link to other Microsoft Office software

  1. Creating References to Other Applications in VBA
  2. Using Tools References to Create a Reference to Word
  3. Using VBA to Link Applications
  4. Using Bookmarks to Fill in a Word Document from Excel (this blog)

This blog is part of a complete Excel VBA tutorial. You can also learn to program in VBA on one of our VBA macros courses.

Posted by Andy Brown on 05 December 2011

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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:

List of people in Excel

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:

Example document in Word

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:

Word form

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:

Word Options dialog box

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:

Inserting Word bookmark

The ribbon option to insert a bookmark

After creating 3 bookmarks, your document should now look like this:

Document with bookmarks

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).

Record a macro

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:

Word find menu

Choose which bookmark you want to go to:

Go to bookmark dialog box

Choose to go to a bookmark, then choose one from the list (it doesn't matter which).

Now stop recording:

Stop recording tool

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:

The NewMacros module

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, _


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:

The Excel list of names

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

'change this to where your files are stored

Const FilePath As String = "C:\ajb files\"

Dim wd As New Word.Application

Dim PersonCell As Range

Sub CreateWordDocuments()

'create copy of Word in memory

Dim doc As Word.Document

wd.Visible = True

Dim PersonRange As Range

'create a reference to all the people


Set PersonRange = Range( _

ActiveCell, _


'for each person in list �

For Each PersonCell In PersonRange

'open a document in Word

Set doc = wd.Documents.Open(FilePath & "Word form.docx")

'go to each bookmark and type in details

CopyCell "FirstName", 1

CopyCell "LastName", 2

CopyCell "Company", 3

'save and close this document

doc.SaveAs2 FilePath & "person " & PersonCell.Value & ".docx"


Next PersonCell


MsgBox "Created files in " & FilePath & "!"

End Sub

Sub CopyCell(BookMarkName As String, ColumnOffset As Integer)

'copy each cell to relevant Word bookmark

wd.Selection.GoTo What:=wdGoToBookmark, Name:=BookMarkName

wd.Selection.TypeText PersonCell.Offset(0, ColumnOffset).Value

End Sub

When you run this macro, it should:

  1. Create a new copy of Word in memory, and make it visible; then
  2. 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:

The 3 documents created

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! 



This blog has 0 threads Add post