- Dedicated trainers
- Maximum 6 per class
- Superb courseware
Phone (01457) 858877 or email
REFERENCING WORD AND OTHER APPLICATIONS IN EXCEL VBA
Part four 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
- Using Bookmarks to Fill in a Word Document from Excel (this article)
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 Bookmarks to Fill in a Word Document from Excel
This page shows an example of how to create a mail merge from Word to Excel. It's quite a long example, but should be a great aid to helping understand how to link applications using VBA.
Files Needed
The files that you'll need to get this to work are as follows:
- The Excel workbook containing the people
- The Word document containing the template to be filled in
The macros are written in Excel 2010 and Word 2010, but should work in Office version 2007 also.
The Aim of the Exercise
Suppose that we have a list of people in an Excel worksheet:
Our aim is to create a "letter" in Word addressed to each of these people.
If we succeed, the end result will be 3 documents saved in Word - the one for Tinky Winky will look like this, for example:
The form filled in for Tinky Winky, the second of the 3 people we're writing to.
To do this we'll loop over the people in the list in Excel, creating a document in Word for each.
Preparing the Word Document
We now need to create our Word template form and add bookmarks to it, so that we know where to insert the various fields. The template form begins life as a simple table:
The first name, last name and company name will appear in the table.
The next thing to do is to make sure that we can see any bookmarks that we add.
A bookmark in Word is a flag in a document which you can jump to - like an insertion point.
To view bookmarks, follow these steps:
- From the Word 2010 menu select File -> Options.
- Choose to show Advanced options.
- Tick to display bookmarks.
You can now insert bookmarks - we'll create 3, called FirstName, LastName and Company. For each bookmark, select the following option and fill in the dialog box which appears:
The ribbon option to insert a bookmark
After creating 3 bookmarks, your document should now look like this:
You can see where the 3 bookmarks have been inserted.
After saving this document, you now need to know how to go to a bookmark in Word from within your Excel code.
Recording a Macro to go to a Bookmark in Word
If you happen to know Word Visual Basic inside-out, you can ignore this step!
Firstly, start recording a macro (if you're not sure what recording a macro means or how to do it, see my earlier tutorial on recording macros in Excel).
Click on this tool to start recording a macro.
While recording (it doesn't really matter what you called your macro), choose to find a bookmark:
First choose this option on the right-hand side of the Home tab to find a bookmark.
Choose which bookmark you want to go to:
Choose to go to a bookmark, then choose one from the list (it doesn't matter which).
Now stop recording:
Click on this tool to stop recording your macro.
If you press ALT + F11 to go into Visual Basic, you'll find your recorded macro in the Normal template:
Word by default puts recorded macros into a module called NewMacros.
You can now see your macro, and copy the only line you want from it:
Selection.GoTo What:=wdGoToBookmark, _
Name:="LastName"
You can now close down Word, and write your Excel macro!
Writing the Excel Macro
The final stage is to open your workbook in Excel, and write your macro:
A reminder of what the Excel workbook looks like.
To write your macro, go into VBA and create a reference to Word 2010 as described earlier in this blog. You can then create the macro - here's what it could look like:
Option Explicit
'change this to where your files are stored
Const FilePath As String = "C:\ajb files\"
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.SaveAs2 FilePath & "person " & PersonCell.Value & ".docx"
doc.Close
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
When you run this macro, it should:
- Create a new copy of Word in memory, and make it visible; then
- For each person, create a new document, write in the person's name and company at the correct locations, then save and close it.
The results? Three documents on your hard disk:
What you should see after running your macro - each document contains a filled-in form for a different person.
And that's how you can fill in a Word document from within Excel!
REFERENCING WORD AND OTHER APPLICATIONS IN EXCEL VBA
Part four 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
- Using Bookmarks to Fill in a Word Document from Excel (this article)
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.