[AccessD] Excel object behavior

That is a spectacular find, Stuart ... the beating heart of VB/VBA
Why have I never seen this 'feature' discussed before?
When I look back now over the years ... lol!

Michael R. Mattys
MapPoint & Access Dev
www.mattysconsulting.com

----- Original Message ----- 
From: "Stuart McLachlan" <stuart at lexacorp.com.pg>
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Sent: Monday, September 22, 2008 9:37 PM
Subject: Re: [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
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com