Word Documents from Excel Macros

Written by Allen Wyatt (last updated April 2, 2022)
This tip applies to Excel 97, 2000, 2002, and 2003


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.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Turning Off Synchronous Scrolling

Synchronous scrolling can be a real help when you are working with worksheets that are similar in layout. If your ...

Discover More

Finding the Date Associated with a Negative Value

When working with data taken from the real world, you often have to determine which certain conditions were met, such as ...

Discover More

Self-Adjusting Column Widths

It is important to understand how column widths relate to the margins you may have set in your document. The reason is ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 2 + 8?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.