Say you wish to create and then close a linked excel sheet:
Dim shp As shape
Set shp = Application.ActiveDocument.Shapes.AddOLEObject(ClassType:="Excel.Sheet")
DoEvents
shp.OLEFormat.Object.Close
This fails with this error:
Run time error '1004'
Close method of Workbook class failed
Why? I can't seem to find any examples of this occurring in word, the closest example I can find is this, which is more related with the user form than the actual function.
The error seems very generic, is there any way to get a more specific reason "why" the close method is failing? It seems if you google around, you'll find this error is thrown for all sorts of reasons(example, another example) but non of these seem to have anything to add to this particular issue.
Note: A similar error occurs with "shp.OLEFormat.Object.Save"
The usual reason for this question is to get the object to "unselect" on the document surface...
In my experience, it's not possible to close a workbook activated on the document surface. This has to do with how OLE Embedding works. Application.Quit should work, but doesn't (in my experience) with Excel.
The most reliable way to achieve this has been to force the workbook to open in an independent Excel.Application window, then you can close and save the workbook and Quit the Excel application.
Something like the following should work:
Dim shp as Word.Shape
Dim oleF as Word.OLEFormat
Dim xlBook as Excel.Workbook 'or Object
Dim xlApp as Excel.Application 'or Object
Set shp = Application.ActiveDocument.Shapes.AddOLEObject(ClassType:="Excel.Sheet")
Set olef = shp.OLEFormat
oelf.DoVerb VerbIndex:=wdOLEVerbOpen
Set xlBook = olef.Object
Set xlApp = xlBook.Application
'Do things here
xlBook.Close
xlApp.Quit
Set xlApp = Nothing
Set xlBook = Nothing
Note that for debugging purposes you might want to set xlApp.Visible = True.
It can happen that xlBook.Close still generates an error, even though the behavior other than that is correct. In that case the error can be surpressed by turning off error messages for this one line, re-instating it following:
On Error Resume Next
xlBook.Close
On Error GoTo 0