Modify embedded Excel workbook in Word document via VBA

Question

I have a Word document with two embedded Excel files (added using Insert -> Object -> Create From File) which I wish to modify using Word VBA. I have got to the point where I am able to open the embedded files for editing (see code below), but am unable to get a handle on the Excel workbook using which I can make the modifications and save the embedded file. Does anyone have a solution for this? Thanks in advance.

Sub TestMacro()

    Dim lNumShapes As Long
    Dim lShapeCnt As Long
    Dim xlApp As Object
    Dim wrdActDoc As Document

    Set wrdActDoc = ActiveDocument

    For lShapeCnt = 1 To 1 'wrdActDoc.InlineShapes.Count
        If wrdActDoc.InlineShapes(lShapeCnt).Type = wdInlineShapeEmbeddedOLEObject Then
            If wrdActDoc.InlineShapes(lShapeCnt).OLEFormat.ProgID = "Excel.Sheet.8" Then
                'This opens the embedded Excel workbook using Excel
                wrdActDoc.InlineShapes(lShapeCnt).OLEFormat.Edit
            End If
        End If
    Next lShapeCnt

End Sub

Answer 1

I have a solution to my own problem. Any further comments will be appreciated -

Sub TestMacro()

    Dim lNumShapes As Long
    Dim lShapeCnt As Long
    Dim xlApp As Object
    Dim wrdActDoc As Document

    Set wrdActDoc = ActiveDocument

    For lShapeCnt = 1 To 1 'wrdActDoc.InlineShapes.Count
        If wrdActDoc.InlineShapes(lShapeCnt).Type = wdInlineShapeEmbeddedOLEObject Then
            If wrdActDoc.InlineShapes(lShapeCnt).OLEFormat.ProgID = "Excel.Sheet.8" Then
                wrdActDoc.InlineShapes(lShapeCnt).OLEFormat.Edit
                Set xlApp = GetObject(, "Excel.Application")
                xlApp.Workbooks(1).Worksheets(1).Range("A1") = "This is A modified"
                xlApp.Workbooks(1).Save
                xlApp.Workbooks(1).Close
                xlApp.Quit
            End If
        End If
    Next lShapeCnt

End Sub

Answer 2

Yikes, don't do what you're suggesting in your comment. You'll probably end up with multiple instances of Excel (check Task Manager and see how many there are after executing your code).

Firstly, add a reference to the Excel object library (Project->References & choose Microsoft Excel Object Library). Now you can declare your objects as bona-fide Excel types and use early binding rather than declaring them as "Object" and using late binding. This isn't strictly necessary, but apart from anything else it means you get Intellisense when editing your code.

You're doing the right thing right up until you do .OleFormat.Edit. (I would personally use .OleFormat.Activate but since I've never tried using .Edit I couldn't say that it makes a difference).

Having done .Activate (or, presumably, .Edit), you can then access the OleFormat.Object member. Since the embedded Object is an Excel chart, the "Object" will be the Excel Workbook, so you can do this:

Dim oOleFormat as OleFormat
Set oOleFormat = ...

oOleFormat.Activate

Dim oWorkbook As Excel.Workbook
Set oWorkbook = oOleFormat.Object

' Do stuff with the workbook
oWorkbook.Charts(1).ChartArea.Font.Bold = True

Note that you do NOT need to close Excel, and indeed you cannot - Word "owns" the instance used for an edit-in-place, and will decide when to close it. This is actually something of a problem, since there's no obvious way to force the embedded object to be de-activated, so the chart would stay open after you execute the code above.

There is a hack-y way to get the chart to close, though. If you add tell Word to activate it as something else, it'll de-activate it first. So, if you tell it to activate it as something non-sensical, you'll achieve the right result because it'll de-activate it and then fail to re-activate it. So, add the following line:

oOleFormat.ActivateAs "This.Class.Does.Not.Exist"

Note that this will raise an error, so you'll need to temporarily disable error handling using On Error Resume Next. For that reason, I normally create a Deactivate method, to avoid disrupting the error handling in my main method. As in:

Private Sub DeactivateOleObject(ByRef oOleFormat as OleFormat)
    On Error Resume Next
    oOleFormat.ActivateAs "This.Class.Does.Not.Exist"
End Sub

Hope this helps. Gary