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

  1. Creating References to Other Applications in VBA
  2. Using Tools References to Create a Reference to Word (this blog)
  3. Using VBA to Link Applications
  4. 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:

Tools References menu

Choose the menu option shown above

The dialog box which then appears shows references that have already been set up for you:

List of standard Excel references

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:

Microsoft Word in references list

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:

Word reference selected

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:

Word reference selected

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.

 

This blog has 0 threads Add post