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:

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