This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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 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.

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

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

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.

Parts 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

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC