SaveAs with embedded Word document - OLEObjects

[christoph123]

Hi,

I have a Word Document embedded as OLEObject in an Excel sheet. What I want to do is to store this embedded object as a file on the disc using VBA.

I use the following code to access and store the embedded object:


Dim oleObject As Object
Dim wordDocument As Object

Set oleObject = ActiveWorkbook.Sheets(sheetName).OLEObjects(1)

Set wordDocument = oleObject.Object

wordDocument.SaveAs ("some filename")


The previous code works well if double-click the embedded OLE object, thus get the focus, and then deactivate it by clicking on the Excel sheet. However, after a re-start of Excel, it doesn't work, so I have to double-click the object and then click on the sheet again in order to get it work. Otherwise I get a runtime error.

I also tried to .Activate and set the .Verb property, with no success. Anybody has a solution for that?

Thanks,
Christoph

[Aussiebear]

Are you sure that this is all the relevant code?

[christoph123]

Yes, pretty sure. I made a simple excel file for you: Just click the button, this will execute the code that I posted. I tried Office 2000 and 2003. If I click the button after start up of Excel, it fails. After double-clicking the OLE object and then clicking the sheet (so we lose the focus on the OLE object), the code works and I get a Word document on the disc.

[Bob Phillips]

This seems to work



Private Sub CommandButton1_Click()
Dim oleObject As Object
Dim wordDocument As Object

Set oleObject = ActiveWorkbook.Sheets("Sheet1").OLEObjects(1)
oleObject.Verb Verb:=xlPrimary
ActiveSheet.Range("A1").Select

Set wordDocument = oleObject.Object

wordDocument.SaveAs ("C:\test.doc")

End Sub

[christoph123]

Thank you. That's working fine.