[AccessD] Excel object behavior

I'm doing things in the following sequence:


Set xlApp = CreateObject("Excel.Application")
Set xlDestBook = xlApp.Workbooks.Open(strTempPath & strReportName)
Set xlOrigBook = xlApp.Workbooks.Open(strTempPath & strFileName)
Set xlDestSheet = xlDestBook.Worksheets("DataSource")
Set xlOrigSheet = xlOrigBook.Worksheets("qryTurnoverByEncloserType")

> Do a bunch of stuff here <

Set xlDestSheet = Nothing
Set xlOrigSheet = Nothing
xlDestBook.Save
xlDestBook.Close
xlOrigBook.Close
Set xlOrigBook = Nothing
Set xlDestBook = Nothing
xlApp.Quit
Set xlApp = Nothing

When I place just these lines in a module (having stripped out all the in between stuff) the instance dies immediately on xlApp.Quit.  When the code runs as written, it doesn't die until the function exits.  I'm thinking I have something in that "in between" that prevents the death of the instance.  I'm combing thru all that to see if something odd is happening there.


-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Steve Goodhall
Sent: Wednesday, September 24, 2008 3:07 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Excel object behavior

Are you executing an Application.quit method on the Excel task before
clearing the pointers?
Regards,
Steve Goodhall, PMP
248-505-5204 mobile


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

Shamil,

Well, I don't know if I have a problem or not.  I was trying to troubleshoot
a user complaint, and noticed that they had a ghost instance of Excel.  When
I stepped through the code, I noticed that the timing of the "death" of the
instance didn't coincide with the setting of the final pointer to Nothing.
When I did a simple test procedure that started and terminated an Excel
instance, I noticed that the instance died immediately upon setting it to
nothing.  So it leads me to think that the user's complaint might be related
to the way the objects are being terminated.  I don't understand much about
that (though, thanks to your help I now understand more) so I raised the
question to the experts.

Thanks again for taking time to respond to my musings . . .

Don

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

Don,

I'm not sure I understand what is the issue? - if you tell that "the
instance DOES go away" but in the same time you tell that you're
"overlooking a pointer somewhere that needs to be set to Nothing."...

I mean if MS Excel instance does go away - that's OK IMO, and everybody
should be happy even without setting all the pointers to nothing :)...

What am I missing?

Thank you.

--
Shamil

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

Shamil,

Thanks for the detailed explanation.  And yes, I was watching the processes
tab of the task manager.  I was careful to make sure that there were no
Excel instances present before running the procedure.  As I step through the
part of the code that cleans up (closing workbooks, quitting Excel, setting
pointers to Nothing, etc.) the instance remains on the processes list.  Only
upon execution of the "Exit Function" line of the procedure does the
instance disappear - but the instance DOES go away, it doesn't remain a
ghost.

I suspect that I'm overlooking a pointer somewhere that needs to be set to
Nothing.  I thought I got them all, but maybe not . . .

Don


--
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

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