Deactivating OLEObject with VBA breaks ribbon in Word 2007

Question

I am writing a script that loops through the embedded excel sheets in my document with VBA. I activate them, do some modifications and go on with the next one. Afterwards, I want the last sheet to be deactivated again and I want the cursor to return to the start of the document.

I have the following code so far:

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

Sub AutoOpen()
    Dim lNumShapes As Long
    Dim lShapeCnt As Long
    Dim xlApp As Object
    Dim wrdActDoc As Document

    Set wrdActDoc = ActiveDocument

    For lShapeCnt = 1 To wrdActDoc.InlineShapes.Count
        If wrdActDoc.InlineShapes(lShapeCnt).Type = wdInlineShapeEmbeddedOLEObject Then
                Dim oOleFormat As OLEFormat
                Set oOleFormat = wrdActDoc.InlineShapes(lShapeCnt).OLEFormat
                oOleFormat.Activate
                DeactivateOleObject oOleFormat
        End If
    Next lShapeCnt
End Sub

I borrowed the deactivation code from Gary McGill. However, this method of deactivation breaks the ribbon in Word 2007.

I can imagine that it would be nicer to reactivate the main document instead of deactivating the OLEObject, but adding wrdActDoc.Activate doesn't seem to do this.

Is it possible to deactivate the excel worksheet without breaking the ribbon?

Answer 1

I don't agree with "tricking" Word like that with Gary's code you reference. See my other post on Update embedded excel file programmatically for how to safely deactivate (but know that it is SendKeys, so it will never be 100% perfect).