Refreshing Excel data in a Word document, using bookmarks

• Download a sample here.

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 following problems:

• 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 Word document
• 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:

Bookmark	Sheet	Range Name
Bookmark1001	Main Data	PL_01
Bookmark1002	Summary Data	CF_02

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 names.

The code for the procedure is shown below. First, the declarations…

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 page.

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 environment (Alt+Q)
• 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.

  Home | About | Links | Contact | Excel | Access