Word Documents from Excel Macros

Eric has an Excel database of company information. He wants to use an Excel macro to copy addresses and information from the database into different Word documents. Eric is curious as to how he can make an Excel macro open a specific Word document into which the information will be pasted.

One way to accomplish this task is to just not use Excel. Instead, use Word's mail merge feature to pull information from an Excel database. This approach works best if you are creating a document from well-defined information. If, however, you need to open a series of documents and copy the data from the Excel database into the documents, then mail merge won't do the trick.

Word has a special name for using macros to work with different Office applications: Office Automation. Creating Office Automation macros is a bit more complex than creating a macro that will work solely within a specific application, such as Excel. One of the things you may want to do is to download a free Help file that includes a good deal of information about Office Automation applications. You can download the file at the following Microsoft page:

http://support.microsoft.com/kb/302460

The basic procedure to open a Word document from within an Excel macro is to create an object that references the Word application, and 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'll need to change the path and document name of the document you want to open, but this code very nicely demonstrates what needs to be done to open the document. As written, the Word document (indeed, the entire Word application) will not be visible on screen. If you prefer to have the application visible, you should use this code line near the beginning of the macro:

    wdApp.Visible = True

Another approach to working with a Word file from inside your Excel macro is to use DDE and the SendKeys function to copy the information. Consider the following DDE command:

ChannelNumber=Application.DDEInitiate{ _
  app:="WinWord", topic:=FullPath

The DDEInitiate method uses two properties: app and topic. The app property indicates the application you are opening via DDE. Typical examples could be "calc" for the calculator or "WinWord" (in this case) for the Word application. The topic property indicates the full path to the document file you are opening. In this case, the full path is contained in the FullPath variable.

Using this method, you can open a document and then use SendKeys to copy information to that document:

Sub PasteExcel2Word()
    Dim channelNumber As String  'Application Handle
    Dim FullPath As String

    FullPath = 'C:\MyFolder\MyFile.Doc'
    'Replace above with a file or loop of files

    Selection.Copy  'Assumes you hilighted what you want copied

    channelNumber = Application.DDEInitiate( _
      app:="WinWord", topic:=FullPath
    SendKeys "^v", False

    Application.DDETerminate channelNumber
End Sub

The Copy method is used to copy information to the Clipboard, and then SendKeys uses ^v (Ctrl+V) to paste the information into the Word documented opened using DDEInitiate.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2423) applies to Microsoft Excel 97, 2000, 2002, and 2003.