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

[jimjones.be]

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

[macropod]

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

[jimjones.be]

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 ?

[macropod]

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

[jimjones.be]

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 :)

[jimjones.be]

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:
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

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 :

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

Sorry about the bad word :)

[Marius Titul]

This is more complicated then I thought.
I'm on it for over an hour and it still doesn't work.

[brienstock]

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(ClassTy pe:="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.