There comes a magical moment in every Excel Visual Basic programmer's life when he - or she - suddenly realises just how wide their horizons are. Which is a poetic way of saying: VBA isn't limited just to Excel!
Talking to Word (say) from within Excel
Wouldn't it be great if you could (say) copy information from Excel into a Word document programmatically? However, the following diagram proves this isn't possible - doesn't it?
If you could create a reference to Microsoft Word from within Excel, it would be included in this list. Wouldn't it?
However, it is possible to expand Excel's horizons so that it knows about Word:
Here we somehow have made available the Word object library within Excel - the rest of this tutorial shows how to do this, and how to use the resulting reference.
Microsoft Applications you can Link to
You can link to the following applications using VBA (among many, many others):
Application | Notes |
|---|
Word | Microsoft Word has unpredictable VBA, but its saving grace is that you can record macros in Word, just like in Excel. |
PowerPoint | You can program presentations and slides in PowerPoint VBA, but you can't record macros. |
Access | I've written a separate tutorial showing how to add, edit, delete and list records and fields in an Access database from within Excel. |
SharePoint | SharePoint Designer supports VBA (although as for PowerPoint and Access, you can't record macros). |
Outlook | You can write macros within Excel to manipulate mail messages and contacts held in Outlook. |
This is just a tiny selection of the applications which support VBA (the technical phrase is that they support the Component Object Model).
Our Initial Example
We'll write a macro which takes a set of jugglers (well, why not?) and lists them out in a new Word document:
Initially we'll have a list of jugglers in an Excel worksheet.
When we run our macro it will create a list of the same jugglers in Word:
The end result of running the macro we'll write: a list of the jugglers in Word. Clearly we could have done something more exciting with them, like creating a table or chart.
Creating References
The first thing that you need to do in your Excel macro is to create a reference to Word, so that we can then create Word documents and type Word text from within Excel.
Note that there is an alternative way to create references to applications using CreateObject and GetObject. Most people should use the method shown on this page, however.
To create a reference to another application, choose the menu option shown below:
Choose this option in the VBA Editor.
The dialog box which then appears shows references that have already been set up for you:
The ticked references at the top of the list show that by default Excel can refer to itself (Excel 365 corresponds at the time of writing to Excel 16.0 using internal Microsoft numbering) and three other libraries containing things common to all Office applications.
If you want to link to Word, the reference you want to find is called Microsoft Word followed by a version number. On my laptop it's:
It takes a long time to scroll down to find the reference you want. The only tip I can give is to click in the list and type M to go to all the references which begin with that letter - then it's up to you to scroll down to find the right one!
You may be wondering at this point what Word 16.0 is. Here's how the versions of Microsoft Office software are really numbered:
Version | Internal number |
|---|
2016, 2019, 2022, 365 | 16.0 |
2013 | 15.0 |
2010 | 14.0 |
2007 | 12.0 |
2003 | 11.0 |
2002/XP | 10.0 |
2000 | 9.0 |
Microsoft omitted version 13.0 for reasons of superstition (probably their customers', as I can't see Bill Gates letting triskaidekaphobia get the better of him).
If you want to check your reference, just choose Tools -> References from the menu again:
References that you have ticked move to the top of the list.
Where and How are References Stored?
If you create references within Excel, these are stored within the workbook. This means that if you were to copy this file to someone else, that person would be able to run your macro - provided that they had the right object library installed.
An object library contains the definition of all of the collections, objects, methods and properties in an application.
You can see the name of the object library for a reference when you select it:
The Word 16.0 reference is shown selected above.
Note that it isn't the folder path that's used to find the object library, but rather a registry entry which specifies which folder the library was installed into. This means that even if you have (say) Word installed in different folders on different machines, you'd still be able to share workbooks that reference MSWORD.OLB.
Creating a New Instance of Word
The first thing our program needs to do is to create a reference to a new Word application in memory. We'll store this reference in a variable. You can do this in one of two ways. The first way is easier to understand:
Dim WordApp As Word.Application
Set WordApp = New Word.Application
However, the second way is easier to write:
Dim WordApp As New Word.Application
So which is better? The short answer is the second one, I think, because it's simpler - but it does have implications. What follows is a fairly technical summary of what these are.
Using the NEW Keyword - Pros and Cons
Consider the following line of code:
Dim WordApp As New Word.Application
This does not set the variable to a reference to Word, it just means that the variable will be set to a reference to Word the first time that it is used. There are two small disadvantages to this:
There is a slight overhead as the variable is checked to see if WordApp is Nothing every time it is used;
Consequently you can never explicitly test if WordApp is Nothing because the instant you do, the variable is instantiated.
IMHO you don't need to worry about this, but at the owlery we try not to gloss over things!
Making an Application Visible
The next thing to do is to make sure that you can see the copy of Word:
You might be wondering why this is necessary. When you create a copy of an application in VBA, it isn't automatically visible. Had you missed out this line, you would have had to press ALT + CTRL + DEL to list out running processes to close it down:
Select the running copy of MS Word - called WINWORD.EXE - and end the task.
Note that the copy of Word you've created programmatically will NOT show up in the Apps section at the top of this list.
Writing Code within the "Foreign" Application
Once you have created a copy of Word within memory, you can run commands within this. Here is what our full macro could look like:
Sub ListJugglersInWord()
Dim WordApp As New Word.Application
WordApp.Visible = True
Dim doc As Word.Document
Set doc = WordApp.Documents.Add
Dim JugglerRange As Range
Dim JugglerCell As Range
Set JugglerRange = Range( _
Range("A1"), Range("A1").End(xlDown))
For Each JugglerCell In JugglerRange
WordApp.Selection.TypeText JugglerCell.Value
WordApp.Selection.TypeParagraph
Next JugglerCell
MsgBox "You should now see your jugglers in Word!"
End Sub
The commands to type information into Word are:
WordApp.Selection.TypeText JugglerCell.Value
WordApp.Selection.TypeParagraph
It is vital that you include the WordApp object at the start of every Word VBA command, as explained below.
The Importance of Including the Application Prefix
Supposing that you had missed out the WordApp object from the 2 lines shown above, to get:
Selection.TypeText JugglerCell.Value
Selection.TypeParagraph
This will not compile or run - here's why. When Excel sees the word Selection, it tries to find this in its list of referenced object libraries:
Excel will start from the top and work its way down in order. It will find the word Selection within the Excel object library before it finds it within the Word one, and so assume that this is a built-in Excel object.
Because Excel is above Word in the list of references, the compiler will assume that Selection refers to the currently selected cells in Excel, and complain loudly that you can not apply the TypeText method to an Excel range:
The error message you will get if you try to run the macro without prefixing the Word Selection object.
In theory you could change the order of the references to put Word above Excel, but apart from being terrible practice - after all, you're coding within Excel - this isn't actually possible (you get an error message if you try to demote Excel in the references list).
A Longer Worked Example
Now we've got all of the theory out of the way, let's look at a worked example of how to get an Excel workbook to fill in a Word form. It's quite a long example, but should be a great aid to helping understand how to link applications using VBA. The files that you'll need to get this to work are as follows:
The macros are written in older versions of Excel and Word, but should work in the latest ones just as well.
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, first choose to show them in Word's Options dialog box (select File => Options to see this):
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:
You can also press F5 to choose this option.
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 as described earlier. 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!
