Referencing Word and other applications in Excel VBA
Part three 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
  3. Using VBA to Link Applications (this blog)
  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 VBA to Link Applications

To get our program to work, we first need to create a reference to a new instance of Word, then use this to create a document.  Finally, we need to loop over our cells typing the values of each into this new Word document.

Creating a New Instance of Word

The first thing 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:

'create a variable to refer to a copy of Word

Dim WordApp As Word.Application

'now set this variable to refer to a new app

Set WordApp = New Word.Application

However, the second way is easier to write:

'set variable to refer to a a new copy of Word

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:

'set variable to refer to a a new copy of Word

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:

  1. There is a slight overhead as the variable is checked to see if WordApp is Nothing every time it is used;
  2. 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:

'make sure this copy of Word is visible

WordApp.Visible = True

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:

Task Manager showing Word process

Select the running copy of MS Word - called WINWORD.EXE - and end the process.

Note that the copy of Word you've created programmatically will NOT show up in the Applications tab of this dialog box.

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()

'set variable to refer to a new copy of Word

Dim WordApp As New Word.Application

'make sure this copy of Word is visible

WordApp.Visible = True

'create a new document in this

Dim doc As Word.Document

Set doc = WordApp.Documents.Add

'loop over all of the jugglers

Dim JugglerRange As Range

Dim JugglerCell As Range

Set JugglerRange = Range( _

Range("A1"), Range("A1").End(xlDown))

For Each JugglerCell In JugglerRange

'for this juggler, write name into Word

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:

'for this juggler, write name into Word

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:

'for this juggler, write name into Word

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:

List of references in order

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:

Error message - property or method not supported

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

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.

 

This blog has 0 threads Add post