Consulting

Results 1 to 8 of 8

Thread: leaving an embedded object (excel sheet) and returning back to word

  1. #1

    leaving an embedded object (excel sheet) and returning back to word

    Hi all,

    i have been looking into this myself but cant find a way to make it work.
    I have a word document with embedded excel tables (formulas, external references) and to make sure they get updated when the document is opened i wrote a bit of code to select them
    <code>
    For Each s In ActiveDocument.InlineShapes
    If s.Type = wdInlineShapeEmbeddedOLEObject Then

    If InStr(1, s.OLEFormat.ProgID, "Excel") Then
    s.OLEFormat.Activate
    End If

    End If
    Next
    <\code>

    This is executed upon opening the document. Problem is the focus stays on the last opened table and i cant find a way to release it.

    Any help?
    PS : this is word 2003

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    You could use:
    OLEFormat.Object.Application.Quit
    Sub Test()
    Dim s As InlineShape
    For Each s In ActiveDocument.InlineShapes
      With s
        If .Type = wdInlineShapeEmbeddedOLEObject Then
          If InStr(1, .OLEFormat.ProgID, "Excel") Then
            .OLEFormat.Activate
            .OLEFormat.Object.Application.Quit
          End If
        End If
      End With
    Next
    End Sub
    or
    Sub Test()
    Dim s As InlineShape
    For Each s In ActiveDocument.InlineShapes
      With s
        If .Type = wdInlineShapeEmbeddedOLEObject Then
          With .OLEFormat
            If InStr(1, .ProgID, "Excel") Then
              .Activate
              .Object.Application.Quit
            End If
          End With
        End If
      End With
    Next
    End Sub
    Last edited by macropod; 11-11-2020 at 04:19 PM.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    Thanks !
    Just what i was looking for.
    And pretty elegant code too

    inlineshape.oleformat.close was of course way too obvious ...

    The code works perfect but ... if i put it in document-open upon opening the document the last table stays selected ?
    Last edited by jimjones.be; 07-24-2012 at 12:50 AM.

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    The code works perfect but ... if i put it in document-open upon opening the document the last table stays selected ?
    That's because of the nature of activating the object. If you want the selection to go somewhere else you'd need to say so. For example:
    Sub Test()
    Dim s As InlineShape, Rng As Range
    With ActiveDocument
      Set Rng = .Bookmarks("\Sel").Range
      For Each s In .InlineShapes
        With s
          If .Type = wdInlineShapeEmbeddedOLEObject Then
            With .OLEFormat
              If InStr(1, .ProgID, "Excel") Then
                .Activate
                .Object.Application.Quit
              End If
            End With
          End If
        End With
      Next
      Rng.Select
    End With
    End Sub
    Last edited by macropod; 10-12-2022 at 04:40 AM.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #5
    It defies reason.
    Even after switching to another inlineshape (image) the excel module stays open (i can tell because when opening the vba editor it looks excel-like).
    Guess i'll have to live with it.

    Thanks for the help, the document does get updated on opening, all that is needed before printing is one click somewhere outside the table.

    i attached the doc in case you wanted to have a look
    Attached Files Attached Files

  6. #6
    Well i found a solution so i thought i might as well post it :

    this is the relevant section :

    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:
    [vba]oOleFormat.ActivateAs "This.Class.Does.Not.Exist"[/vba] 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:
    [vba]Private Sub DeactivateOleObject(ByRef oOleFormat as OleFormat)
    On Error Resume Next
    oOleFormat.ActivateAs "This.Class.Does.Not.Exist"
    End Sub[/vba]
    i got this here : (sorry cant post links)
    stackoverflow.com/questions/483813/modify-embedded-excel-workbook-in-word-document-via-vba

    so the final code would be something like this :

    [vba]Private Sub Document_Open()

    Dim s As InlineShape
    With ActiveDocument
    For Each s In .InlineShapes
    With s
    If .Type = wdInlineShapeEmbeddedOLEObject Then
    With .OLEFormat
    If InStr(1, .ProgID, "Excel") Then
    .Activate
    On Error Resume Next
    .ActivateAs ("bull****")
    End If
    End With
    End If
    End With
    Next
    End With

    End Sub[/vba]

    Sorry about the bad word

  7. #7
    This is more complicated then I thought.
    I'm on it for over an hour and it still doesn't work.
    Last edited by Aussiebear; 08-14-2023 at 02:21 PM. Reason: Edited signature to remove spam link

  8. #8

    Thumbs up DeactivateOleObject: works for current version of Office 365 (2020)

    Quote Originally Posted by Marius Titul View Post
    This is more complicated then I thought.
    I'm on it for over an hour and it still doesn't work.
    I ran into this same issue with an OLE activated Excel object in Word, and I couldn't get the Excel OLE window to close.

    The following works for me:

    Private Sub DeactivateOleObject(ByRef olef As Word.OLEFormat)
        On Error GoTo ErrorHandler
        Call olef.ActivateAs("This.Class.Does.Not.Exist")
        
    ErrorHandler:
    End Sub
    Then the above sub can be used like:

    Dim MyShape As InlineShape
    Set MyShape = wordDocumentInstance.Bookmarks(key).Range.InlineShapes.AddOLEObject(ClassType:="Excel.Sheet")
    
    
    'Do some stuff with OLE object
    
    
    DeactivateOleObject MyShape.OLEFormat
    Note: since the OLE object was created by Word, the data type of the "olef" parameter in the subroutine above is defined as "Word.OLEFormat". If this was the reverse, and an OLE object was created by Excel, then the type would be "Excel.OLEFormat". If you indicate the wrong type for that parameter, then you will get a Type Mismatch error.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •