Run-time error 1004 Method 'Close' of object '_Workbook' failed

[Boomer67]

Hi,

I am using Excel 2003 on XP. I have various processes that open workbooks, do some operations on them and then close them again. This normally works fine, but I occasionally get a problem with the code in that it throws a debug error when it gets to the close stage. The simple code is:

Sub DemonstrateCloseError()
    Workbooks.Open "C:\boomer\Open and Close.xls"
        ' do stuff
    Workbooks("Open and Close.xls").Close savechanges:=True
End Sub

When run the debug error highlights the close line. If I then continue the processing through F8 (step into) or F5 (Run) it continues fine - I do not change anything. This indicates that the code and the file itself are OK, but gives no reason for the halt in processing.

I have even re-installed Excel in an attempt to fix but I still get the problem.

Appreciate if anyone can point me to the cause.

Thanks

 

[JMonty]

What happens if you use the following:

Sub TestOpenAndClose()

    Set wbk = Workbooks.Open("C:\boomer\Open and Close.xlsx")
    wbk.Sheets(1).Range("A20") = wbk.Sheets(1).Range("A20") + 1
    wbk.Close SaveChanges:=True

End Sub

Run it 5 times. The next time you open the file, A20 of the first sheet should have 5 on it.

Let us know if this works.

Thanks,

J.

 

[Boomer67]

Jonty,

Thanks for the reply.

When I try that it runs until it gets to the close command, then halts again. It won't run through to the end even once without the manual intervention.

Regards
Boomer

 

[JMonty]

In the code between opening and closing, is there anything that changes the designations of the workbooks involved? Mind posting the code here?

Thanks,

J.

 

[patel45]

Sub DemonstrateCloseError()
   set wb = Workbooks.Open "C:\boomer\Open and Close.xls"
        ' do stuff
    wb.Close savechanges:=True
End Sub

 

[Boomer67]

Jonty,
There are a frw hundred lines of code so maybe not practical to post them all. Basically the process reads data from the file, uses it in the main calling file, and depending on what it the outcome is it may write back to the Open and Close file.

I have tested it again and with save changes set to false and the code runs fine. I am not opening it as read only, so slightly more confused now.

 

[Boomer67]

I have changed the code from :

Sub DemonstrateCloseError()
    Workbooks.Open "C:\boomer\Open and Close.xls"
        ' do stuff
    Workbooks("Open and Close.xls").Close savechanges:=True
End Sub

to

Sub DemonstrateCloseError()
    Workbooks.Open "C:\boomer\Open and Close.xls"
        ' do stuff
    Workbooks("Open and Close.xls").Save
    Workbooks("Open and Close.xls").Close
End Sub

and this works fine as well. it does seem like a solution but doesn't explain why the original approach does not work.