Accessing Excel Object without using Shape.Activate() on a Word document using VBA

Question

I have a tried reading an embedded excel document in a word document. I followed the code specified at this blog article:

http://vbadud.blogspot.com/2010/08/how-to-read-excel-sheet-embedded-in.html

Dim oWB As Excel.Workbook
Dim oIShape As InlineShape

For Each oIShape In ActiveDocument.InlineShapes
    If InStr(1, oIShape.OLEFormat.ProgID, "Excel") Then
        oIShape.OLEFormat.Activate
        Set oWB = oIShape.OLEFormat.Object
        oWB.Sheets(1).Range("A1").Value = "ProdID"
    End If
Next oIShape

It works fine but the Activate line causes the document to flicker on each excel document I read. I tried to remove the oIShape.OLEFormat.Activate code but it causes the next line to throw a "Runtime error '430' (class does not support Automation or does not support expect).

The question is there any other way to access embedded excel without calling the Activate method?

Daniel Daniel · Accepted Answer · 2012-07-14T06:20:38

This is tricky! The short answer is, no. Not with an embedded Excel.

I did some experimentation and some research. Since I could not find any sources that specifically explained the behavior. this is somewhat a guess on my part. It appears that when you embed the Excel spreadsheet into your word document essentially Word stores a link of spreadsheet, which displays only the appearance because it needs to be interpreted with the Excel program. Until you actually active the shape, you cannot interact with it because that cannot be done with Word directly. This article alludes to the behavior, but doesn't explain it. Here's a quote:

If you edit the object in Word, click anywhere outside the object to return 
to the destination file.
If you edit the object in the source program in a separate window, 
click Exit on the File menu of the source program to return to the
destination file.

You may have noticed that even if you use. Application.ScreenUpdating = false it still does the flickering you mention. This is because you are using a different application when you access the shapes! Every time you active the shape, the object specific menus etc load.

A possible work around: If instead of embedding Excel Spreadsheets via the insert menu, you can instead add a control. On my machine using Office 2003 the comparible one is: Microsoft Office Spreadsheet 11.0 This is technically a web control, but the methods and behavior are very comparable to an Excel workbook.

Using the control instead of the handy inserted object, with a slight variation of your code I was able to comment out your activate command and the code ran as expected. Specifically, I had to change these lines:

Dim oWB As Spreadsheet instead of Excel.Workbook.
If InStr(1, oIShape.OLEFormat.ProgID, "OWC11.Spreadsheet.11") Then instead of "Excel"

Basically you can decide... Activate your embedded object that requires Excel to interpret, or use a different control that doesn't require activation.

Accessing Excel Object without using Shape.Activate() on a Word document using VBA

1 Answers