Object Linking and Embedding still going strong

OLE stands for Object Linking and Embedding and COM was invented to facilitate OLE. Nice to see all the COM interfaces still working.

Inserting a worksheet into Word document

Open Word, create new document go to VBA IDE and paste in the following code to see how to insert a worksheet and script against the worksheet as if you were in Excel VBA.

Option Explicit
'Paste into Word VBA
Sub ObjectLinkingAndEmbeddingExample()

    Dim shp As Shape
    Set shp = ThisDocument.Shapes.AddOLEObject("Excel.Sheet")
    
    shp.OLEFormat.Object.Sheets(1).Cells(1, 1) = "OLE works!"


End Sub

It would appear the gateway through to the Excel object model is given by OLEFormat.Object on the shape class.

Simulating an ActiveX command button click in code

Sometimes the gateway can require two successive calls to Object. In an Excel worksheet go to Developer mode and insert an *ActiveX* command button (not intrinsic Excel button), then double click to get to Click handler code and add the following

Option Explicit

Private Sub CommandButton1_Click()
    MsgBox "CommandButton1_Click"
End Sub

Then come out of developer mode and click the button to ensure the message box is thrown. Then insert the following code in a non-class module and run it.

Option Explicit

Sub SimulateButtonClick()

    '* After adding an *ActiveX* command button on the activesheet

    Dim shp As Shape
    Set shp = ActiveSheet.Shapes(1)
    
    Debug.Assert TypeName(shp.OLEFormat.Object.Object) = "CommandButton"
    
    Dim cmd As CommandButton
    Set cmd = shp.OLEFormat.Object.Object
    
    '* The next line compiles but Value is not is the Intellisense dropdown menu.
    '* It mimics the clicking on the button and should throw the message box in
    '* above handler code (see block above)
    cmd.Value = True

End Sub

Running this code will mimic the button click. You can see how in this case you need two calls to Object to get the plumbing right otherwise you get a type mismatch error