Expose Excel InlineShapes to edit

Question

I am trying to automatize filling in a third party form from an Excel Workbook VBA module. They sadly have gone with a Word document with an in-line Excel workbook embedded, which contains cells as named ranges that I want to edit.

How do I assign the InlineShapes object to an Excel.workbook object so that I can expose it to Excel methods and properties?

This is what I have tried so far:

Sub test()
        Dim wdApp As Word.Application
        Set wdApp = CreateObject("word.application")
        wdApp.Visible = true ' for testing
        Dim wdAppendixB As Word.Document
        Set wdAppendixB = wdApp.Documents.Add(ThisWorkbook.Path & "\Templates\form_template.dotx")
        Dim wbAppB As Excel.Workbook
        wdAppendixB.InlineShapes.Item(1).OLEFormat.Edit
        Set wbAppB = wdAppendixB.InlineShapes.Item(1).OLEFormat.Object
        wbAppB.Sheets("Sheet1").Range("date1").Value = "2019-06-02"
Exit Sub

As soon as the script opens the OLE object for editing, the script stops with no errors. Closing the OLE object for editing does not resume the script.

If I omit editing the object and just set the workbook object to the OLEFormat.Object it errors out with Run-time error '430' "Class does not support Automation or does not support expected interface".

Any suggestion is appreciated.

Cindy Meister Cindy Meister · Accepted Answer · 2019-06-04T17:38:19

Use Activate, instead of Edit (or DoVerb with an appropriate wdOleVerb constant).

Note that this will leave the object in an activated state. There's no elegant way to emulate the user clicking outside the object to de-select it. The workarounds are to either open the object in its own window (instead of in-place) and close that file window OR to try to activate the object as a specific, non-existant class. Since this will trigger an error, this has to be wrapped in `On Error Resume Next' and 'On Error GoTo 0'.

Sub test()
        Dim wdApp As Word.Application
        Set wdApp = CreateObject("word.application")
        wdApp.Visible = True ' for testing
        Dim wdAppendixB As Word.Document
        Set wdAppendixB = wdApp.Documents.Add(ThisWorkbook.Path & "\Templates\form_template.dotx")
        Dim wbAppB As Excel.Workbook
        Dim of As Word.OLEFormat
        Set of = wdAppendixB.InlineShapes.Item(1).OLEFormat
        of.Activate '.Edit
        Set wbAppB = of.Object
        wbAppB.Sheets("Sheet1").Range("B1").Value = "2019-06-02"
        On Error Resume Next
        of.ActivateAs "This.Class.NotExist"
        On Error GoTo 0
End Sub

Expose Excel InlineShapes to edit

1 Answers