WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 519 reviews for our classroom and online training
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! 

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 online Advanced VBA course - wherever you are in the world.

This blog has 5 threads Add post
23 Nov 21 at 11:11

Amazing article, thank you !

But i have a problem: I want in the same bookmark to use two columns (to put someone's first name and surname for example).  And after a bookmark I want to add a string using something like CopyCell "Amount", 1 & " €". Is this possible?

23 Nov 21 at 11:20

To put in the first name and surname, the easiest thing to do is to use two bookmarks.  Likewise with the amount - just add another bookmark, and type what you want in there.

23 Nov 21 at 11:25

I am so stupid. And for my second problem, do you have idea ? 

Andy B  
23 Nov 21 at 11:40

I guess I'm not really sure what you mean.  If you want to paste in the value of something, an easy way to do that is to store the text in a variable in Excel, then just write the contents of the variable in at a Word bookmark as if it were normal text.

27 Oct 20 at 11:45

If i only wanted to create one word document for all instead of individual ones, what would the code look like? Thanks!

27 Oct 20 at 11:56

Just move the first grey line below before the loop, and the last two grey lines after it. 

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

You could probably do with adding a carriage return after copying each set of values - from memory, something like:


22 May 20 at 11:34

Thanks for wonderful article, this is what I was looking for. For some reason when I run macro it shows a mistake on this line:

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

Mistake: "Run-time error '5174'. This file could not be found. It also showed error 462. 

Should it be modified? 

28 Jan 21 at 19:25

I had the same problem because

1) FilePath refers to the path that locates the folder of the user form and not the word.docx you downloaded. When i copied the folder's path i did not realise that the last  \  was missing.
2) At Set doc = wd.Documents.Open(FilePath & "Word form.docx") , "Word form" is the name of the file and when i downloaded it was called "d7". So when i changed to "d7.docx" it worked.

Hope it helped you.

22 May 20 at 16:53

I think the code is good.  You may have some strange characters in your folder or file names?  Try this StackOverflow article for ideas if you're still stuck.

19 May 20 at 16:51

Constantly find myself coming back to this page - thanks so much for creating it WiseOwl.

One thing i've never mangaged to tackle is how you amend this so that the values inserted into the Word bookmarks keep their source fomatting (or merge formatting with the word document) - sometimes it'd be useful to retain or merge that formatting to improve the look/feel of the document. Any ideas?

20 May 20 at 09:36

I'd try this.  Copy something in Excel which includes formatting, then go to Word.  Start recording a macro, and use Paste Special to paste in the cells keeping the formatting you want.  Stop recording, and have a look to see if the code you've got can be tweaked to do what you want.  Feel free to report back if you have any success, for others to use in the future!

18 May 18 at 09:55

This is great!

Does anyone have any idea how to amend this so that if the value that you're copying to the word document is a numerical value?

I.e. if the value you wanted to copy from excel was "15%" if you use this code then the value inserted into the word bookmark reads "0.15".

Is there a way to keep the cell format as it is displayed in the excel document when pasted into the bookmark?



18 May 18 at 11:20

You could use the Text property of the range you're copying, rather than the Value property:

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

I hope that helps!