Refreshing Excel data in a Word document,
- Download a sample
If you are using Word to build a report that
relies on Excel tables, you have a couple of ways to achieve it. One
is to create links to the Excel file, which is OK if the file is not
very large or calculation-intensive, and you don’t have too many
bookmarks. However, you may find that you have one or more of the
- If you do have a large workbook with many
links from the Word document, you will often find that the Word
file takes forever to respond.
- Reproducing the Excel formatting in the
Word document can be tedious and will most likely need to be
updated when you refresh the file
There are articles showing how to push data
from Excel to Word using VBA, [see the following], but I hadn’t come
across any that used Word as the driver when I had a recent need to
do this. The code needed to meet some specific criteria:
- Updating needed to be fast, and maintain
the formatting in the Excel file
- The user wanted to be able to select the Excel source file
at run time
- The code needed to be triggered using an
item on the toolbar.
The approach that I came up with uses
bookmarks in the Word document, matched to range names in the Excel
file. This requires the following:
- A Word document, populated with bookmarks
where the Excel content will be pasted
- An Excel file with the source data, with
a defined range name for every table that will be used in the
- A table in the Excel file with 3 columns,
laid out as in the example below. This table must have a defined
range name. In the code below, it is called Bookmarks, and lives
on the Lists sheet of the Excel workbook. The data could
look something like this:
Note: The sheet name is required because Word cannot reference an
Excel range without knowing which sheet the range is on. Also, watch
out for the range names in Excel. If you like to use 3-letter
acronyms for your ranges, you will have problems updating this code
to Office 2007 because the native format in 2007 goes out to column
XFD, and Excel won’t let you use column or cell references as range
The code for the procedure is shown below. First, the
Sub RefreshAllTables() ''============================================================================== ''Purpose: To refresh the current table in a Word document with new data from '' the corresponding range in an Excel document. ''The code uses bookmarks in the Word document and corresponding named ranges in '' Excel. The Excel data is brought in as pictures. This has the advantage that any '' formatting in the Excel document is retained, and the dimensions don't change '' significantly. '' Also, bookmarks are simpler to create and maintain because a picture is only a '' single character in a Word document. ''Requires: A table in the Excel file to line up the bookmarks and named ranges ''Created: 23 Oct 2008 by Denis Wright ''============================================================================== Dim objExcel As Object, _ objWbk As Object, _ objDoc As Document Dim sBookmark As String, _ sWbkName As String Dim sRange As String, _ sSheet As String Dim BMRange As Range Dim bmk As Bookmark Dim i As Integer, _ j As Integer, _ k As Integer, _ bmkCount As Integer Dim vNames() Dim vBookmarks() Dim dlgOpen As FileDialog Dim bnExcel As Boolean On Error GoTo Err_Handle
The FileDialog object lets you pick a file for processing. It is
placed within a Do … Loop to ensure that the user picks an Excel
file. It could also be modified to ensure that they pick an Excel
file with a table called Bookmarks, located on the List sheet.
Set dlgOpen = Application.FileDialog( _ FileDialogType:=msoFileDialogOpen) bnExcel = False Do Until bnExcel = True With dlgOpen .AllowMultiSelect = True .Show If .SelectedItems.Count > 0 Then sWbkName = .SelectedItems(1) Else MsgBox "Please select a workbook to use for processing" End If End With If InStr(1, sWbkName, ".xls") > 0 Then 'proceed bnExcel = True Else MsgBox "The file must be a valid Excel file. Try again please..." End If Loop
The next step is to open or activate the workbook. This section
checks to see whether the selected file is already open; if not, it
is opened. The error handling routine at the end of the code
launches Excel if it is not already running.
Set objDoc = ActiveDocument 'check to see that the Excel file is open. If not, open the file 'also grab the wbk name to enable switching Set objExcel = GetObject(, "Excel.Application") For i = 1 To objExcel.Workbooks.Count If objExcel.Workbooks(i).Name = sWbkName Then Set objWbk = objExcel.Workbooks(i) Exit For End If Next
Now we minimize the Excel window and hide the application. There
are two arrays to be populated as well. The first uses the Bookmarks
table in the Excel file; the second contains the names of all
bookmarks in the active Word document. We also set an object
reference to the Excel workbook, to simplify the code later on.
If objWbk Is Nothing Then Set objWbk = objExcel.Workbooks.Open(sWbkName) End If 'minimize the Excel window objExcel.WindowState = -4140 'minimized 'switch to Excel, find range name that corresponds to the bookmark objExcel.Visible = False objWbk.Activate vNames = objWbk.Worksheets("Lists").Range("Bookmarks").Value 'loop through the bookmarks bmkCount = ActiveDocument.Bookmarks.Count ReDim vBookmarks(bmkCount - 1) j = LBound(vBookmarks) For Each bmk In ActiveDocument.Bookmarks vBookmarks(j) = bmk.Name j = j + 1 Next bmk
This section loops through all the bookmarks in the array,
retrieving the Excel tables and pasting them into the word document.
A few points are worth commenting on:
There are some quirks to using pictures with bookmarks.
- First, deleting a picture does not delete the bookmark, as
it does with text. This meant that two extra steps were
required. The bookmark needed to be specifically deleted, and
then the code had to backtrack by one character to ensure that
the newly re-created bookmark enclosed the picture instead of
being placed after the insertion point.
- Possibly as a consequence of the bookmarks remaining after
the picture was deleted, it was not possible to step through the
Bookmarks collection for the processing; the routine kept
returning to the first bookmark. That is the reason for loading
the bookmark names into an array, an processing the contents of
the array instead.
Another major frustration is that recording the Paste Special
code to insert the Excel table as an enhanced metafile, was ignored
by Word, which defaulted to inserting the table as a formatted Word
table. This is what we were trying to avoid because it changed the
layout of the Word document. The workaround was to use the
CopyPicture method in Excel, to guarantee that the data was inserted
as a picture.
For j = LBound(vBookmarks) To UBound(vBookmarks) 'go to the bookmark Selection.GoTo What:=wdGoToBookmark, Name:=vBookmarks(j) Set BMRange = ActiveDocument.Bookmarks(vBookmarks(j)).Range For k = 1 To UBound(vNames) If vNames(k, 1) = vBookmarks(j) Then sSheet = vNames(k, 2) sRange = vNames(k, 3) Exit For End If Next k 'copy data from the range as a picture objWbk.Worksheets(sSheet).Range(sRange).CopyPicture 1, -4147 'return to Word and paste objDoc.Activate BMRange.Select Selection.Delete 'Note: only required if the bookmark encloses a picture. 'If the bmk held text, deleting the selection removes the bmk too. 'Under those circumstances the code throws an error. 'Clunky workaround: tell Word to ignore the error On Error Resume Next ActiveDocument.Bookmarks(sBookmark).Delete On Error GoTo 0 'paste the picture, then move back one character so the new bookmark 'encloses the pasted picture Selection.PasteAndFormat (wdPasteDefault) Selection.Move Unit:=wdCharacter, Count:=-1 'now reinstate the bookmark objDoc.Bookmarks.Add Name:=vBookmarks(j), Range:=Selection.Range Next j
The final part is some error handling and a cleanup section to
remove the object references, and redisplay the Excel window.
Err_Exit: 'clean up Set BMRange = Nothing Set objWbk = Nothing objExcel.Visible = True Set objExcel = Nothing Set objDoc = Nothing MsgBox "The document has been updated" Err_Handle: If Err.Number = 429 Then 'excel not running; launch Excel Set objExcel = CreateObject("Excel.Application") Resume Next ElseIf Err.Number <> 0 Then MsgBox "Error " & Err.Number & ": " & Err.Description Resume Err_Exit End If End Sub
This code has been broken up into sections to help with
describing its function. If you want to copy it as a single block,
it is provided on this
To run the code copy it into a new module in your Word document:
- Alt+F11 to go to the VBA environment
- Insert > Module, paste the code, then close the VBA
- You can trigger the macro by pressing Alt+F8 and
double-clicking the macro name. In a later article I will show
how to run the code from a custom menu.