[AccessD] Excel object behavior

Don,

You can check CTRL+ALT+DEL => Task Manager => Processes - and you can see MS
Excel "ghost" instance(s) hanging there - that could be the case for your
customers...

When you create MS Excel instance in VBA code you use what is called "COM
Out-of-Process Server" everything (a lot!) happens "under the hood" - but
your application keeps "reference count" to the MS Excel objects your code
have created - and this "reference counting" is a PITA/bottleneck of the
whole COM technology...

You can check this code will "clean-up"/kill MS Excel well on exit:

Public Sub test()
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
End Sub

and this code will result in a "ghost" MS Excel instance:

Public Sub test()
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.WorkBooks.Add
End Sub

In the fist case VBA/COM runtime is able to automatically kill MS Excel
instance as there are no any other COM objects *referencing* this MS Excel
COM instance...

In the second case VBA/COM runtime *IS NOT* able to decide is it possible to
kill MS Excel instance of not because there is Excel.Workbook object
instance referencing Excel.Application object instance and Excel.Application
object instance indirectly referencing Excel.Workbook object instance
through Excel.Workboos collection...

This code:

Public Sub test()
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Dim wbk As Object
Set wbk = xlApp.WorkBooks.Add
wbk.Close
xlApp.Quit
Set xlApp = Nothing
End Sub

Will properly quits MS Excel instance even without setting wbk object
instance to nothing because when you close wbk the reference count (I guess)
from Workbooks collection is decreased by one, and there are no COM
cross-references, which would keep ("ghost") MS Excel instance from
quitting...


--
Shamil

P.S. Most of us use COM technology everyday - all and every MS Office app
creates a lot of COM objects inside, and most of these applications are COM
out-of-process servers etc. MS programmers who did program these application
were very careful with reference counting - and we have stable an useful set
of application with myriads of COM objects created and destroyed during
their lifetime as MS Windows processes running on our PCs...


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of McGillivray, Don
[IT]
Sent: Tuesday, September 23, 2008 10:19 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Excel object behavior

Thanks, Shamil

As a test I wrote a brief proc that simply opens and then kills an Excel
instance.  Indeed, the instance does  die as soon as its object variable is
set to nothing.  That is NOT happening in my proc, so it looks like I'm
overlooking something there - probably in the way my references are
qualified.  Still, if I haven't correctly terminated that Excel instance,
I'm surprised that it doesn't hang around after the proc finishes.  Seems
like I'm doing SOMEthing right - just not EVERYthing.

Thanks again to all who responded.

Don


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: Tuesday, September 23, 2008 10:51 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Excel object behavior

<<<
Maybe that's normal behavior, maybe not.  Anybody know for sure?
>>>
Don,

For sure it's not normal behavior - you should have somewhere "hanging" MS
Excel object(s), which were not forcibly set to nothing before you quit MS
Excel Application instance, or you might have created a "ghost" instance of
MS Excel if you didn't qualify fully MS Excel objects while using them in
your VBA code - something like Stuart's sample shows...

--
Shamil


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of McGillivray, Don
[IT]
Sent: Tuesday, September 23, 2008 8:27 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Excel object behavior

Thanks to all for the feedback, however, my question is not so much how to
kill the instance (I seem to be doing that OK), but whether the instance
should be surviving to the bitter end of the proc - despite having
quit/closed/set to nothing all related Excel objects well before the end of
the proc.

Maybe that's normal behavior, maybe not.  Anybody know for sure?



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Monday, September 22, 2008 8:08 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Excel object behavior

Yep, it took me a long time to track down.

I had a function which created a Word document from a template and then
stuffed data
into it was.  I cut and pasted code from the Word Macro recorder to do some
formatting of
the data once it was inserted.   It was generally used only once every few
days and  it
always worked fine.  Then when a user tried to call it twice in a row it
broke the second
time around with a 462 error.

I finally tracked it down by googling "Error 462: The remote server machine
does not exist
or is unavailable."




On 22 Sep 2008 at 22:05, Michael R Mattys wrote:

> 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



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com