[AccessD] Excel object behavior

 Don't know about Excel, but I've had a similar problem occur in Word. The problem turned 
out to be using Unqualified References. It's explained at 
http://support.microsoft.com/kb/319832

Here's an example from that page which illustrates your problem:

<quote>
Qualifying the Code to Avoid Errors
The best guideline is to avoid using any Office object that you do not explicitly call from a 
parent object that you set in a specific variable. In other words, look for code that uses 
Office objects without qualifying which Office instance or document that it is supposed to 
refer to. For example, this code uses an unqualified call to display the count of open 
workbooks in Microsoft Excel:

Sub CreateThreeBooks()
   Dim oXL As Excel.Application
   Dim i As Long

 ' Create Excel instance (make it visible for test)...
   Set oXL = New Excel.Application
   oXL.Visible = True
 
 ' Open a few empty workbooks...
   For i = 1 To 3
      oXL.Workbooks.Add
   Next i

 ' How many books did we open?
   MsgBox "Number of workbooks: " & Workbooks.Count, vbMsgBoxSetForeground
   
 ' Shutdown Excel (or do we?)...
   oXL.Quit
   Set oXL = Nothing
 ' Check the Processes list. Excel.exe is still running!
End Sub

When you run the code, it appears to run correctly the first time. However, Excel continues 
to run, even though you called the Quit method. If you call the code again, the message 
box now incorrectly displays 0 for the workbook count. Your code may now fail where you 
would expect it to succeed.

To resolve both problems, you must fully qualify the Workbooks object that you reference 
for the count, as follows:

   MsgBox "Number of workbooks: " & oXL.Workbooks.Count, vbMsgBoxSetForeground
				
</quote>


> From: McGillivray, Don [IT]
> Sent: Mon 9/22/2008 17:10
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Excel object behavior
> 
> 
> Hello, All
> 
> I have a procedure that exports a bunch of data to Excel, and then
> uses the Excel object model to manipulate the resulting spreadsheets
> (creating pivot tables, applying formatting, and the like.)  Usually
> it works as advertised, but some of my users are having a problem
> where the UI freezes (or maybe it just appears to freeze). 
> 
> In trying to isolate the problem, I watched the effects of my code
> carefully, and I notice that the Excel instance that I create in code
> doesn't terminate until the procedure itself is finished.  This,
> despite the fact that I have closed, quit, and set to nothing all
> Excel related objects before the end of the procedure.  I know through
> experience that failing to terminate Excel objects correctly leaves
> the instance alive even at the end of the procedure.  So I assume that
> I'm killing them correctly since the instance does indeed die. 
> 
> Can anybody tell me if I should expect to see the instance survive
> until the end of the proc, or is there something else I ought to be
> doing when I'm finished with Excel? 
> 
> Thanks!
> 
> Don McGillivray
> 
-- 
Stuart Mclachlan