3

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"

Community
  • 1
  • 1
David Rogers
  • 2,601
  • 4
  • 39
  • 84

2 Answers2

3

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
Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
  • That makes sense, however when I run the code you posted it still gives me the original error. I think if I suppress the error it still closes the workbook though, maybe that's the solution I go with going forward. I wonder if you have any insight into why that occurring though? – David Rogers Mar 29 '16 at 18:41
  • See if `xlBook.Close False` makes any difference. That's in the code in my book that I took this from, written for 2013 and it ran without errors in testing. I left it out because I thought it wouldn't make any difference, but perhaps there was a good reason I had it in there, originally :-) And no, no idea why the error, exactly, although if you look in the File menu of the Excel application you will see it's something like "Close and update". The default is likely "True" and Excel cannot save the file, which could be the reason for the error. – Cindy Meister Mar 29 '16 at 18:44
  • Note: if it's working otherwise, you can suppress that error with a `On Error Resume Next` before it then an `On Error GotTo 0` following the line. – Cindy Meister Mar 29 '16 at 18:45
  • `xlBook.Close False` Doesn't seem to change anything, I still get the error and I tested in on two different machines, maybe it could be something environmental, or some other small difference I haven't though of yet, anyways suppressing the error(with `On Error Resume Next`) seems to work fine, so at least now I have working code. Thanks for the help :) – David Rogers Mar 29 '16 at 18:55
  • Glad it's working for you :-) I've added a summary of this discussion to the Answer. – Cindy Meister Mar 29 '16 at 18:58
1

Here's what I came up with:

Set xlBook = olef.Object
Set xlApp = xlBook.Application

xlApp.SendKeys "{ESC}"

This will stop the Excel.Application and return the user to Word. Same thing as running the commanding to close the Workbook.

David Rogers
  • 2,601
  • 4
  • 39
  • 84
ChrCury78
  • 427
  • 3
  • 8