Update embedded excel file programmatically

Question

I'm trying to modify an embedded excel table in a word document programmatically. To do this, I have modified the docx file and the embedded excel file.

The significant part of the main document is the following:

<w:object w:dxaOrig="8406" w:dyaOrig="2056">
  <v:shape id="_x0000_i1028" type="#_x0000_t75" 
    style="width:390.75pt;height:95.25pt" o:ole=""><v:imagedata r:id="rId14" 
    o:title=""/>
  </v:shape>
  <o:OLEObject Type="Embed" ProgID="Excel.Sheet.12" ShapeID="_x0000_i1028" 
    DrawAspect="Content" ObjectID="_1349794876" r:id="rId15" 
    UpdateMode="Always"/>
</w:object>

The word document uses an OLEObject to link to the embedded excel document. For displaying purposes, a .wmf file is used (using the v:shape element). I have modified the excel document, which outdated this preview.

This results in some strange behaviour in the document:

• The preview of the embedded (excel) table shows the wrong data
• Double clicking on the embedded table opens the table in an embedded excel and shows the correct data
• Closing the embedded editor triggers the generation of a new preview, showing the correct data

Of course, I want the table to show the correct table when the document is opened. How can I trigger Word to discard the image and redraw the preview?

An ideal solution for me would be to trigger the regeneration of the preview just by modifying the contents of the docx, but solutions using a small script would also help.

Answer 1

There is no perfect solution to this, but one that works most of the time is to force an open/close of the OLEFormat.Object. It doesn't matter if you are rehydrating the embedded Excel worksheet from outside of Word (i.e. manipulating the Open XML format) or doing it through the object model. What it involves is opening the embedded Excel spreadsheet from within Word and then closing that object for the image to be changed to the current values in the embedded spreadsheet and the new image to be created.

It depends just a bit if you are doing this on many Word documents or just one. In the former case, a global template (such as normal.dot or a custom one you create and put in the STARTUP folder) or in the later case, just run code behind of one document. Both have a different way to getting things to run, but essentially you will be hooking the Document_Open event and from there checking if the current document has OLE Embedded objects and if so, opening and closing them.

Without going into the hook, like I said, the code isn't pretty. Basically because it uses SendKeys. Most of the time, this will work. Some of the time, it won't. That is the nature of SendKeys and other programs receiving the focus without consent (such as an instant messenger program).

If Word has the focus (which means you can't open the VBE and press F5), this code should do the trick:

Sub UpdateEmbeddedXLSX()
Dim workbook As InlineShape
    For Each workbook In ActiveDocument.InlineShapes
        With workbook
            If .Type = wdInlineShapeEmbeddedOLEObject Then
                ''# Excel.Sheet.12 for Excel 2007
                If .OLEFormat.ClassType = "Excel.Sheet.12" Then
                    ''# Open Object as spreadsheet
                    .OLEFormat.DoVerb wdOLEVerbPrimary
                    ''# If you want, you can also do any updates here like
                    .OLEFormat.Object.ActiveSheet.Cells(2, 2).Value = ".1"
                    ''# Nasty - but it works - SendKeys
                    SendKeys "{ESC}", True
                End If
            End If
        End With
    Next
End Sub

At the very least, you could put this code in your normal.dot and assign it to the QAT to be run as a macro.

Note that the code doesn't get around Excel opening, the values changing and then closing - that is part and parcel of using embedded objects. Using linking instead of embedding would be a much smoother way to do all of this, but I realize it's not always an option.

Answer 2

Just to add to an old post in the event someone stumbles upon this like I did:

The above code works great, but I modified it to use bookmarks instead of using SendKeys. The SendKeys statement really messes with the NumLock on my keyboard. Just one of the quirks of that command.

What I did was create bookmarks in my Word Doc Template. Then in my code, I created a pointer to the bookmark:

Dim bMark as bookmark
Set bMark as ActiveDocument.Bookmarks("NameOfBookmark")

Then in place of the SendKeys statement, I did the following:

bMark.Range.Select
Selection.EndKey

This basically pulled the focus out of the embedded worksheet, and onto the bookmark of the page. Then the .EndKey statement simply removed the selection. You don't really even need it.

Hope this helps!