560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Referencing Word and other applications in Excel VBA
Part two 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
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 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:
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.
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.
|Parts of this blog|
25 Aytoun Street