Create Word Documents from Excel Macros

by David Hakala

Suppose you have a database of company information and contacts. You want to use an Excel macro to copy addresses and such into different Word documents. How can you make Excel open a specific Word document into which information will be pasted?

Word’s mail merge feature is one solution. But if you are working with multiple Word documents, the mail merge trick won’t work. You will need something more complicated called Office Automation, which allows creation of macros that work with multiple Office applications and not just one, such as Word.

You may want to download Microsoft’s Office XP Automation Help file to learn the theory of Office Automation and practice a few examples.

The basic procedure to open a Word document from within an Excel macro is to create an Excel VBA object that references the Word application, then use that object to open the document. The following code illustrates this concept:

Sub OpenWord()

Dim wdApp As Object

Dim wdDoc As Object

Set wdApp = CreateObject(“Word.application”)

Set wdDoc = wdApp.Documents.Open _

(FileName:=”C:\Path\myTestDoc.doc”)

‘ put your code here for working with Word

‘ This is Word VBA code, not Excel code

wdDoc.Close savechanges:=False

Set wdDoc = Nothing

wdApp.Quit

Set wdApp = Nothing

End Sub

You will need to change the path and document name, but this example nicely illustrates how to open a Word document in an Excel worksheet. As it is written, this example runs Word invisibly. If you want to see Word doing its thing, just add this line at the beginning of the macro code:

wdApp.Visible = True

David Hakala has perpetrated technology tutorials since 1988 in addition to committing tech journalism, documentation, Web sites, marketing collateral, and profitable prose in general. His complete rap sheet can be seen at http://www.linkedin.com/in/dhakala