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:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2423) applies to Microsoft Excel 97, 2000, 2002, and 2003.
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!
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2020-05-25 07:24:23
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
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
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
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
@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
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
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2020 Sharon Parq Associates, Inc.
Comments