Manipulate an Excel (OLE) embedded object from within Word 2007 using VBA

I am not fully done accomplishing what I wanted to do, but since this took me the better part of 2 hours to figure out I figured I would post my findings to my blog in the hopes of Google finding them to help out others…

Basically I have an embedded Excel worksheet in my word document, which I want to be able to dynamically change depending on the value of a checkbox.  For example, if the checkbox is ticked, I want to hide a row in in the excel checkbox.  This was a lot more difficult than I thought it would be…

First, to manipulate the excel object, I need to find out its InlineShape index number, then use this code to activate it:

ActiveDocument.InlineShapes(9).OLEFormat.Activate

From there would have been as easy as just using VB to hide the row, but the entire function needs to be called thus: ActiveDocument.InlineShapes(9).OLEFormat.Object.ActiveSheet.Rows(“2:2″).EntireRow.Hidden = True

This obviously needed to be simplified so I came up with this:

Now this would be all fine and dandy, but document will definitely be changed at some point in the future, thus the chances of my InlineShape index number dereferencing itself was pretty much guaranteed.  I finally figured out how to use a bookmark to reference the object, so that no matter what happens (short of someone messing up the bookmarks of course), the index number for the object will stay constant.

To set the object as a bookmark, simply select the object, go to insert and insert bookmark, then add a unique name.  For my bookmark, it was named ‘isnTest’, without the ’s.  What this now does is creates another index of inlineshapes that starts with 1, which basically means that this bookmark and the index 1 for InlineShapes will always refer to my object.

Here is my final solution for hiding a row in an embedded excel document in word 2007:

Set xlObj = ActiveDocument.Bookmarks(“isnTest”).Range.InlineShapes(1).OLEFormat

xlObj.Activate

xlObj.Object.ActiveSheet.Rows(“2:2″).EntireRow.Hidden = False