Closing an Excel Object

PearlGI

Registered User.
Local time
Today, 00:41
Joined
Aug 30, 2001
Messages
125
I've got a database that creates an Excel object, outputs data, formats and then saves the Excel file. Excel then closes.
The problem is the Excel object hasn't been fully closed as it still appears within the NT Task Manager process list. This causes the code to crash if it's then run a second time.
Below is an extract of the code used to create and close the object. What needs changing to fully close the object.


Public objExcel As Object

Set objExcel = CreateObject("Excel.Application")

{processes}

objExcel.Quit
Set objExcel = Nothing


Any help gratefully received.
 
Seen something similar where tasks aren't removed from the task list after they complete for a while.
It's a feature of NT. Check the NT FAQ's.
Ideas: Investigate obj.save followed by obj.close so the task manager realises the task is complete and doesn't keep the process hanging about. If I recall Quit is 'get out now where Close is get out saving changes .. may be wrong, long day ..
 
See also the post: http://www.access-programmers.co.uk/ubb/Forum7/HTML/002348.html
My responses there basically say:
Export the file as an Excel spreadsheet
Launch Excel to do what you want to it
(using auto_open Excel VBA code in a 'master' spreadsheet
Close Excel and the spreadsheet get's closed tidily and promptly
One other thought: Is Access holding the object open? Does the object lock dissappear if you re-start your database?
If so, creating a seperate spreadsheet rather than an object from Access is the way to go.
 
Thanks again, MrT.

It is Access that's holding Excel open, because when Access is closed the excel.exe process disappears.

Unfortunately, the solution you suggest will not solve my problem as the VBA needs to completely control the Excel processes involved (without user intervention). The data that's being output to Excel comes from 19 different queries (record counts may vary from month-to-month) and so to accomplish with the suggested method would be extremely complex and messy.


Thanks once again.

Any other suggestion? Anybody?
frown.gif
 
hmm...
The model we've used allows a lot of re-formatting and re-calculation based on triggers in the master spreadsheet and the extracted data.
It just uses VBE rather than VBA. The users don't get an option to interfere, they just see the VBE doing it's work.
VBE will work just as well as VBA with no user intervention. If you need to refer to data within your Access db, you can do that from VBE or just export the data from Access for VBE to use.
 
hmm...
The model we've used allows a lot of re-formatting and re-calculation based on triggers in the master spreadsheet and the extracted data.
It just uses VBE rather than VBA. The users don't get an option to interfere, they just see the VBE doing it's work.
VBE will work just as well as VBA with no user intervention. If you need to refer to data within your Access db, you can do that from VBE or just export the data from Access for VBE to use.
 

Users who are viewing this thread

Back
Top Bottom