Loading
Excel.Tips.Net ExcelTips (Menu Interface)

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/default.aspx?scid=kb;en-us;302460&product=offxp

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.

Related Tips:

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

 

Comments for this tip:

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

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 3+4? (To prevent automated submissions and spam.)
 
          Commenting Terms
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2013)

Our Products

Premium Newsletters

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2014 Sharon Parq Associates, Inc.