Server Throws Exception on Workbook.Close

[russian_hamlet]

I have a simple MS Word macro that opens up MS Excel files, grabs
tables or graphs and inserts them into the MS Word file. It works fine
on my computer (MS Windows 2000, MS Office 2000 SP3) and some of my
colleagues' (MS Windows 2000 and one MS Windows XP, MS Office 2000
SP3), but produces a runtime error on others' (MS Windows XP, MS Office
2000 SP3).
On trying to .Close certain files, we get a "Server threw an exception"
error: -2147417851 (80010105) on some systems.

I can provide more detailed code if need be, but this is the framework
....

' ... Subroutine
Public objExcel As Excel.Application '... have tried late-binding, with
Object
Public objWorkbook As Excel.Workbook
Private ExcelRunning As Boolean

Sub HookExcelObject()
On Error GoTo ExcelNotRunning
ExcelRunning = True
Set objExcel = GetObject(, "Excel.Application")
Exit Sub
ExcelNotRunning:
ExcelRunning = False
Set objExcel = CreateObject("Excel.Application")
End Sub

Sub UnhookExcelObject()
If Not ExcelRunning Then
objExcel.Quit
End If
Set objExcel = Nothing
End Sub

' ... Main Module, behind a form
Call HookExcelObject

strFile = "c:\file_1.xls"
Set objWorkbook = objExcel.Workbooks.Open(FileName:=strFile,
Updatelinks:=False, ReadOnly:=True)
objWorkbook.Sheets(strSheet).Range(strRange).Copy
objWorkbook.Close SaveChanges:=False

strFile = "c:\file_2.xls"
Set objWorkbook = objExcel.Workbooks.Open(FileName:=strFile,
Updatelinks:=False, ReadOnly:=True)
objWorkbook.Sheets(strSheet).Range(strRange).Copy
objWorkbook.Close SaveChanges:=False ' <<< CRASH, BANG, WALLOP!

Set objWorkbook = Nothing
Call UnhookExcelObject

If I halt the code at the .Close line, I can probe the two objects
fine.

? TypeName(objExcel), TypeName(objWorkbook)
Application Workbook
? objExcel.ActiveWorkbook.Name, objWorkbook.ActiveSheet.Name
my_workbook.xls my_Sheet
objExcel.Visible = True
objWorkbook.Activate
objWorkbook.Close SaveChanges:=False
<BOOOOOOOM>

objWorkbook.Saved = True
objWorkbook.Close
<BOOOOOOOM>

objExcel.Visible = True
objWorkbook.Close(False)
<BOOOOOOOM>

Could this be a MS Window XP thing ?
Any possible pointers?
thanks
jON

 

[NickHK]

Not really an answer, but if you omit the "SaveChanges:=False" argument, do
you get excel dialog show up asking to save or not (assuming some change has
actually occurred) ?

NickHK

 

[russian_hamlet]

Now I'm really confused.
If I take out SaveChanges:=False, then the naughty file, file2, asks if
I want to save changes, even though I have not made any changes, even
though I have remmed out the code that does anything at all with the
file after opening it. Click No and it bombs.
What confused me was what happened next, though ...
Just for fun, I switched file1 and file2 around. Then file1 bombed.
This - the fact that every second file was bombing - suggested that
perhaps I was not releasing objWorkbook properly. To test this theory,
I switched file1 and file2 back round and remmed out the file2 code
entirely. Now file3 should bomb, right? Wrong. The code runs fine for
file1, file3 and file4.
Errrr ...

 

[NickHK]

Sure there's no code running in file1 or file2, Autpen/Close or
WB_Open/Close events ?

NickHk

 

[russian_hamlet]

Absolutely not.

 

[NickHK]

Don't know what to say, assuming there file_1 and file_2 are no different to
the 3 and 4 files.
No query tables or reasons why the WB should be in the middle of some action
?

NickHK

 

[russian_hamlet]

All the files are different. File3 contains a macro, but nothing that
should affect this.
I have tried the files in various orders ...

File Order > Bombs at
1234 > 2
123 > 2
134 > no bomb
1324 > 2
1342 > 2
2134 > 1
2413 > 1
4132 > 2
4321 > 1

The logic seems to be: (1) never bomb the first file, (2) bomb the
first File1 or File2 moving backwards from the end, i.e. utter rubbish.
I therefore suspect the files are up the wrong tree, so to speak. There
is nothing unusual about them, anyway.
And there is nothing obviously wrong in the code. It even works on some
(Windows 2000) machines.
I am worried that I'm perhaps not cleaning up my objects properly,
although I have stopped the code before and after each file closes and
it always gives a objExcel.Workbooks.Count of 1 and then 0 ...

 

[NickHK]

What if you run the code from Excel instead of from Word .
See what happens.

NickHK

 

[russian_hamlet]

I had to rem out most of it, but what was left threw me an error on
file2 (original 1234 order) ...
Automation error -2147417848 (80010108)
The object has disconnected from its clients

 

[NickHK]

Sounds like you will need to be a bit more "creative" to trace the cause..
Do these help ?

http://www.mrexcel.com/archive2/20000/22741.htm
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319832

NickHK