BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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
- Creating References to Other Applications in VBA
- Using Tools References to Create a Reference to Word (this blog)
- Using VBA to Link Applications
- Using Bookmarks to Fill in a Word Document from Excel
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 Tools References to Create a Reference to Word
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.
Creating References
To create a reference to another application, choose the menu option shown below:
Choose the menu option shown above
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:
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 14.0 is. Here's how the versions of Microsoft Office software are really numbered:
| Version | Internal number |
|---|---|
| 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).
Checking the Reference Created
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 2010 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.
Having created a reference to Word, we can now refer to it in programming code, as explained in the next part of this blog.
- Creating References to Other Applications in VBA
- Using Tools References to Create a Reference to Word (this blog)
- Using VBA to Link Applications
- Using Bookmarks to Fill in a Word Document from Excel