Word Documents from Excel Macros

by Allen Wyatt
(last updated April 21, 2020)

8

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

Changing the Pattern Used in a Data Series

When you create a chart, Excel attempts to assign colors to your data series that it thinks will work best for you. If ...

Discover More

Deleting a Hyperlink

Hyperlinks can be helpful in some worksheets but bothersome in others. Here's how to get rid of any hyperlinks you don't ...

Discover More

Using Microsoft Fix It

Got a program on your system that you can't get rid of? Here's a handy little program that Microsoft provides that may be ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 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}] 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 6 - 0?

2020-05-25 07:24:23

Barry

New version with improved error handling & progress indicator.

Download from here: https://tinyurl.com/yajxnmk2

THis downloaded 2 files (zipped) the Excel file with database and macros, and an example Word template file.


2020-05-02 08:20:04

Barry

I've noticed that the formatting has been lost in my last post, and in particular some longer lines have been split onto two lines. Copying the code as is without correcting these formatting changes will caused VBA to give errors.


2020-05-02 08:16:45

Barry

The following code will merge data on worksheet with a Word Document see sample workbook & Word document in this zip file:
https://drive.google.com/open?id=1GxryPRpBxXnzcza_FxY_TvmxFfOzEqB3

Please take particular attention to the notes a the beginning of the macro:

Sub PopulateForm()
'Requirements:
'1. set references in Excel to "Microsoft Word 14.0 Object Library" and Microsoft Office 14.0 Object Library"
'2. must have template form document called myForm.doc and located in the same folder as the Excel spreadsheet
'3. the "myForm.doc" must have the same number of "Formfields" as the spreadsheet has columns with data
'4. data for the forms is held on worksheet named "Data", and has a header row.

Dim temp As String
Dim i As Integer
Dim j As Integer
Dim fPath As String
Dim fForm As String
Dim fNew As String
Dim appWD As Word.Application
Dim sht As Worksheet

fPath = ThisWorkbook.Path & "\"
Set sht = Worksheets("Data")
fForm = "myForm.doc"

On Error Resume Next
MkDir fPath & "CompletedForms"
On Error GoTo 0

Set appWD = CreateObject("Word.Application")

For i = 2 To sht.Range("A" & sht.Rows.Count).End(xlUp).Row
appWD.Documents.Open fPath & fForm
fNew = "CompletedForm - " & sht.Range("A" & i).Value & " - " & Format(Date, "d-mmm-yy") & ".doc"
appWD.Documents(fForm).SaveAs fPath & "CompletedForms\" & fNew
For j = 1 To sht.Cells(1, sht.Columns.Count).End(xlToLeft).Column
appWD.Documents(fNew).FormFields("Text" & j).result = sht.Cells(i, j).Text
Next j
appWD.Documents(fNew).Close savechanges:=True
Next i
appWD.Quit
Set appWD = Nothing
MsgBox sht.Range("A" & sht.Rows.Count).End(xlUp).Row - 1 & " records merged!", vbOKOnly, "COMPLETED!"
End Sub


2020-04-30 06:57:13

H

Dear Mr Wyatt

Thank you for your effort and your informative replies ...
I have no experience in using Vb in cell and I have that situation ..

I need a macro for Excel, that will pull data from say, Column A, Column C, Column D, Column E, etc.. and enter that information onto a Word Doc. or PDF. with the data in a list ... then save that document with the Name from Column A.

The file I have will have 200 rows... therefore I will need to create 200 word docs, each saved with the name from Column A (this is the customer/learner name)

So ... Mayo you help me?


2018-10-11 23:45:36

Anthony Fantano

Both VBR's OpenWord & PasteExcel2Word don't work for me. I already fixed the pat/to/file but still not working. All I get is a blink of a window and nothing more.


2017-02-17 05:41:43

Barry

@Janet,

There are a couple of ways to get information into a Word Document from an Excel macro. My preferred method is to insert "formfields" into the Word Document (if you have a lot of fields I suggest renaming them with something meaningful - I've Word document with over 200 fields in it that I populate from Excel). (There are different types of formfield so you need to insert the correct one for the type entry you want Excel to make). Then once Excel has opened the document use can use the following code to move information into a text type formfield:

wdDoc.FormFields("Text24").Result = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value

the expression to the right of the "=" sign can be any valid Excel expression. This can then be repeated for any number of fields.

If you want to have checkboxes and tick/untick them then the following code will do this (obviously you need to insert in your document a formfield checkbox:

wdDoc.FormFields("Check6").CheckBox.Value = TRUE

you can use an Excel expression to the right but it must evaluate to either TRUE or FALSE.


2017-02-16 11:00:08

Janet

This article was excellent for what I need it for however I would like to go a little further and export fields in excel to words without to use Ctrl +V to create a log. Could you help me in this regards


2015-07-12 11:53:40

Javier Gonzalez

Hi,

thank you very much for this tip, very interesting and useful!

The name of the Word document is not known and fixed, but actually I need the user to select a Word file. Would you know how to integrate such user interaction?


Thanks a lot in advance,

Javier


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.