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:
Dim WordApp As Word.Application
Set WordApp = New Word.Application
However, the second way is easier to write:
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:
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:
- There is a slight overhead as the variable is checked to see
if WordApp is Nothing every
time it is used;
- 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:
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:
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()
Dim WordApp As New Word.Application
WordApp.Visible = True
Dim doc As Word.Document
Set doc = WordApp.Documents.Add
Dim JugglerRange As Range
Dim JugglerCell As Range
Set JugglerRange = Range( _
Range("A1"), Range("A1").End(xlDown))
For Each JugglerCell In JugglerRange
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:
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:
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:
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:
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.
