Macro 97: Sending Excel Data to a Word Document
If you find that you are constantly copying and pasting data into Microsoft Word, you can use a macro to
automate this task.
How it works
Before walking through the macro, it’s important to go over a few set-up steps.
To get ready for a process like this, you must have a template Word document already created. In that
document, create a bookmark tagging the location where you want your Excel data to be copied.
To create a bookmark in a Word document, place your cursor where you want the bookmark, select
the Insert tab, and select Bookmark (found under the Links group). This activates the Bookmark dia-
log box where you assign a name for your bookmark. After the name has been assigned, click the
Add button.
You also need to set a reference to the Microsoft Word Object Library. To do so, open the Visual Basic
Editor in Excel and select Tools➜References. The Reference dialog box opens. Scroll down until you
find the entry Microsoft Word XX Object Library, where the XX is your version of Word. Select the
check box next to the entry.
Sub Macro97()
‘Step 1: Declare your variables
Dim MyRange As Excel.Range
Dim wd As Word.Application
Dim wdDoc As Word.Document
Dim WdRange As Word.Range
‘Step 2: Copy the defined range
Sheets(“Revenue Table”).Range(“B4:F10”).Copy
‘Step 3: Open the target Word document
Set wd = New Word.Application
Set wdDoc = wd.Documents.Open _
(ThisWorkbook.Path & “\” & “PasteTable.docx”)
wd.Visible = True
‘Step 4: Set focus on the target bookmark
Set WdRange = wdDoc.Bookmarks(“DataTableHere”).Range
‘Step 5: Delete the old table and paste new
On Error Resume Next
WdRange.Tables(1).Delete
WdRange.Paste ‘paste in the table
‘Step 6: Adjust column widths
WdRange.Tables(1).Columns.SetWidth _
(MyRange.Width / MyRange.Columns.Count), wdAdjustSameWidth
‘Step 7: Reinsert the bookmark
wdDoc.Bookmarks.Add “DataTableHere”, WdRange
‘Step 8: Memory cleanup
Set wd = Nothing
Set wdDoc = Nothing
Set WdRange = Nothing
End Sub
1. Step 1 declares four variables: MyRange contains the target Excel range you want copied;
wd is an object variable that exposes the Word Application object; wdDoc is an object
variable that exposes the Word Document object; and wdRange is an object variable that
exposes the Word Range object.
2. Step 2 copies a range from the Revenue Table worksheet. In this example, the range is hard-
coded, but we can always make this range into something more variable.
3. Step 3 opens an existing target Word document that serves as a template. Note that we are
setting the Visible property of the Word application to True. This ensures that we can
see the action in Word as the code runs.
4. Step 4 uses Word’s Range object to set focus on the target bookmark. This essentially selects
the bookmark as a range, allowing you to take actions in that range.
5. Step 5 deletes any table that may exist within the bookmark, and then pastes the copied
Excel range. If we don’t delete any existing tables first, the copied range is appended to the
existing data.
6. When you’re pasting an Excel range into a Word document, the column widths don’t always
fit the content in the cells appropriately. Step 6 fixes this issue by adjusting the column
widths. Here, each column’s width is set to a number that equals the total width of the table
divided by the number of columns in the table.
7. When we paste an Excel range to the target bookmark, we essentially overwrite the book-
mark. Step 7 re-creates the bookmark to ensure that the next time you run this code, the
bookmark is there.
8. Finally, the macro releases the objects assigned to the variables, reducing the chance of any
problems caused by rogue objects that may remain open in memory.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code in the newly created module.
