URGENT! Expunge Excel from Memory

[Perico]

Is there a fool proof way to expunge Excel from memory? I'm manipulating
Excel from Access 2003. Even when I use the following methods, I'm still
finding Excel.exe in Process in the Windows Task Manager:

Dim xlAppMst As Excel.Application
Dim xlBookMst As Excel.Workbook
Dim xlSheetMst As Excel.Worksheet

Set xlAppMst = CreateObject("Excel.Application")
Set xlBookMst = xlAppMst.Workbooks.Add
Set xlSheetMst = xlBookMst.Worksheets(1)

xlSheetMst.Visible = True
xlSheetMst.Cells.Select
Selection.NumberFormat = "@"

xlSheetMst.SaveAs fNameMst
......other code here

xlBookMst.Close
Set xlSheetMst = Nothing
Set xlBookMst = Nothing
xlAppMst.Quit
Set xlAppMst = Nothing

 

[Jim Cone]

It is very easy to leave "orphan" references when automating Excel that prevent
the application from quitting. One of the ways to create these is using
unqualified references such as "Selection", "ActiveWorkbook", and "ActiveSheet".
Use object references for all objects in Excel and Set them to nothing when exiting.

Also, you are changing the number format on 17 million cells.
You probably don't want to do that.

You can handle the selection and number format issue with something
like this...
Dim xlRangeMst as Excel.Range
Set xlRangeMst = xlSheetMst.Range("A1:F1000") 'you specify
xlRangeMst.NumberFormat = "@"
xlAppMst.Visible = True

'check the rest of your code for unqualified references
'also eliminate any use of "With".

Set xlRangeMst = Nothing
Set xlSheetMst = Nothing
xlBookMst.Close
Set xlBookMst = Nothing
xlAppMst.Quit
Set xlAppMst = Nothing
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Perico" <[email protected]>
wrote in message
Is there a fool proof way to expunge Excel from memory? I'm manipulating
Excel from Access 2003. Even when I use the following methods, I'm still
finding Excel.exe in Process in the Windows Task Manager:

Dim xlAppMst As Excel.Application
Dim xlBookMst As Excel.Workbook
Dim xlSheetMst As Excel.Worksheet

Set xlAppMst = CreateObject("Excel.Application")
Set xlBookMst = xlAppMst.Workbooks.Add
Set xlSheetMst = xlBookMst.Worksheets(1)

xlSheetMst.Visible = True
xlSheetMst.Cells.Select
Selection.NumberFormat = "@"

xlSheetMst.SaveAs fNameMst
......other code here

xlBookMst.Close
Set xlSheetMst = Nothing
Set xlBookMst = Nothing
xlAppMst.Quit
Set xlAppMst = Nothing

 

[Tom Ogilvy]

Make sure you qualify every reference to an object all the way down from the
xlAppMst object or an object that is derived from xlAppMst.
Then release the references in reverse order.

Make sure all references are release (your problem now - they all are not
released - possibly because you created a unqualified reference by refering
to an object without fully qualifying it.).

 

[Perico]

These are great suggestions. Thank you both very much!