- Dedicated trainers
- Maximum 6 per class
- Superb courseware
Phone (01457) 858877 or email
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
- Creating References to Other Applications in VBA
- Using Tools References to Create a Reference to Word
- Using VBA to Link Applications (this article)
- 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.
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:
- 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:
'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:
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:
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.
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
- Creating References to Other Applications in VBA
- Using Tools References to Create a Reference to Word
- Using VBA to Link Applications (this article)
- 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.
Comments on this blog
This blog has 5 comments:
Btw, I think it should be "A2" here instead of "A1," so it doesn't create a Word file for the first row of the Excel sheet.
'create a reference to all the people
Range("A1").Select
Also, I think it's worth mentioning that you have to close the Word document before trying the run the micro, otherwise it'll return an error message saying that the Word document is locked for editing.
just my 2 cents :) awesome blog btw, thank you!!
Thanks for those thoughts, and glad you liked the blog!
Hi guys
First of all thank for the admin for the greatest post, even though it seems small thing for him but for who needs it, it is a greatest thing :-) - THANKS A LOT!!!!:-)
For those who want to export word to pdf using excel vba here is the code - It will help for those who search lot because I did.
Just simple modification:
MODIFIED CODE
doc.ExportAsFixedFormat FilePath & "person " & PersonCell.Value & ".pdf", wdExportFormatPDF
doc.Close False
ORIGINAL CODE
doc.SaveAs2 FilePath & "person " & PersonCell.Value & ".docx"
doc.Close
_____________________________________________________
HERE IS THE COMPLETE CODE OF IT
Option Explicit
'change this to where your files are stored
Const FilePath As String = "C:\Documents and Settings\thiruselvam\Desktop\Macro\exit interview\"
Dim wd As New Word.Application
Dim PersonCell As Range
Sub CreateWordDocuments()
'create copy of Word in memory
Dim doc As Word.Document
wd.Visible = True
Dim PersonRange As Range
'create a reference to all the people
Range("A1").Select
Set PersonRange = Range(ActiveCell, ActiveCell.End(xlDown))
'for each person in list �
For Each PersonCell In PersonRange
'open a document in Word
Set doc = wd.Documents.Open(FilePath & "Word form.docx")
'go to each bookmark and type in details
CopyCell "FirstName", 1
CopyCell "LastName", 2
CopyCell "Company", 3
'save and close this document
doc.ExportAsFixedFormat FilePath & "person " & PersonCell.Value & ".pdf", wdExportFormatPDF
doc.Close False
Next PersonCell
wd.Quit
MsgBox "Created files in " & FilePath & "!"
End Sub
Sub CopyCell(BookMarkName As String, ColumnOffset As Integer)
'copy each cell to relevant Word bookmark
wd.Selection.GoTo What:=wdGoToBookmark, Name:=BookMarkName
wd.Selection.TypeText PersonCell.Offset(0, ColumnOffset).Value
End Sub
Thanks
Thiru
Thanks for that, Thiru.
this blog has been of endless help for me.
I have experience with VB6, not so much with VBA for Excel though.
However I tried to get into it and now I tried to apply the above code to one of my worksheets. It works fine - every other time, at least.
Every second time I trigger the program it gives me the:
Run-time error '462': The remote server machine does not exist or is unavailable.
and the debugger highlights the line "wd.Visible = True".
Yes, I tried to find a solution online, but I am afraid my thus far aquired knowledge is not yet sufficient enough to apply any solutions I could find :/
Please find the Code below. Thanks for your help!
Option Explicit
Const FilePath As String = "C:\Users\Bonik\Documents\FH\6. Semester\vba#\"
Dim wd As New Word.Application
Dim PersonCell As Range
_______________________________________________________________________
Sub CreateDocuments()
Dim doc As Word.Document
wd.Visible = True
Dim PersonRange As Range
Set PersonRange = Range(Cells.Find("Vorname").Offset(1, 0), Cells.Find("Vorname").End(xlDown))
For Each PersonCell In PersonRange
Set doc = wd.Documents.Open(FilePath & "test2.docx")
Call CopyCell("Vorname", 0)
Call CopyCell("Nachname", 1)
Call CopyCell("Firma", 2)
doc.SaveAs2 (FilePath & PersonCell.Value & ".docx")
doc.Close
Next PersonCell
wd.Quit
MsgBox ("Letters are saved here:" & vbCrLf & FilePath)
End Sub
_______________________________________________________________________
Sub CopyCell(BookMark As String, CellOffset As Integer)
wd.Selection.GoTo What:=wdGoToBookmark, Name:=BookMark
wd.Selection.TypeText PersonCell.Offset(0, CellOffset).Value
End Sub
I'm not sure, but I think your problem may be that you're creating a new Word application in the declarations section of the module. Try first changing the variable declaration to:
Dim wd As Word.Application
Then in your CreateDocuments procedure, add the line:
Set wd = New Word.Application
It will then create a new Word application whenever you run the program. I can't think of anything else which might cause this problem.
By adding:
Set wd = Nothing
at the very end right after wd.Quit the problem dosen't show up anymore.