Programmatic way to remove Excel from Memory

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Perico - 23 Oct 2006 04:36 GMT

I'm using automation to run some Excel vba, then using the
transferspreadsheet method in Access to import data from Excel. The
transferspreasheet method results in Excel staying in memory after the
procedure completes. (Tests establish this; it is not due to the automation
code.)

Is there a VBA way to programmatically remove excel from memory so the user
doesn't have to go into the Windows Task Manager Processes tab?

Reply to this Message

Dirk Goldgar - 23 Oct 2006 05:13 GMT

> I'm using automation to run some Excel vba, then using the
> transferspreadsheet method in Access to import data from Excel. The
[quoted text clipped - 5 lines]
> the user doesn't have to go into the Windows Task Manager Processes
> tab?

TransferSpreadsheet doesn't normally leave an instance of Excel in
memory -- I don't think it even loads the Excel application, though I
could be mistaken -- so your problem must be due either to an error in
the automation code, or to the combination of the automation code with
TransferSpreadsheet.

I suggest you make sure that the automation code saves the workbook and
closes Excel before you call the TransferSpreadsheet method. If you do
that, try setting a breakpoint after the code that quits the Excel
application, and before calling TransferSpreadsheet. When the code
stops at that line, check in Task Manager to make sure that Excel is
really no longer in memory. Then let the code continue. If you find
Excel in memory after that, I'll be very surprised.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

Perico - 06 Nov 2006 22:35 GMT

This import automation code resolved my issue of running Excel vba from
Access. It's interesting that on my export routine (not shown here) I had to
use a messagebox between the first automation routine and the second one to
avoid a "-2147417851" automation error. It may have something to do with
synchronous vs async mode and COM?

Dim oApp As Object
Dim oWb As Object

On Error Resume Next
Set oApp = CreateObject("Excel.Application")
Set oWb = oApp.Workbooks.Open(vFullPath)
If oWb Is Nothing Then
MsgBox "Workbook Not Found or Error! Exiting.", vbCritical, "Error"
Set oWb = Nothing
Set oApp = Nothing
Exit Sub
End If
On Error GoTo err_

oApp.Visible = True
oApp.Application.windows(vFile).Visible = True

If resType = "Res" Then
oWb.Application.Run "cycleRes"
ElseIf resType = "Nres" Then
oWb.Application.Run "cycleNRes"
End If

oWb.Close False
Set oWb = Nothing

oApp.Quit
Set oApp = Nothing

'=============================================
DoCmd.SetWarnings False
DoCmd.OpenQuery "10Flush_tmpNewInoivce"
DoCmd.OpenQuery "11Flush_tmpNewHeader"
DoCmd.SetWarnings True

'overwrites tmpNewInvoice and tmpNewHeader
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, vTableNameData,
vFullPath, True, vRangeData
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
vTableNameHeader, vFullPath, True, vRangeHeader

'-----
Dim oApp2 As Object
Dim oWb2 As Object

On Error Resume Next
Set oApp2 = CreateObject("Excel.Application")
Set oWb2 = oApp2.Workbooks.Open(vFullPath)
If oWb2 Is Nothing Then
Set oWb2 = Nothing
Set oApp2 = Nothing
MsgBox "Workbook Not Found or Error. Exiting.", vbCritical, "Error"
Exit Sub
End If
On Error GoTo err_

oApp2.Visible = True
oApp2.Application.windows(vFile).Visible = True

'-----
If resType = "Res" Then
oWb2.Application.Run "REVERSEcycleRes"
ElseIf resType = "Nres" Then
oWb2.Application.Run "REVERSEcycleNRes"
End If

oWb2.Close False
Set oWb2 = Nothing

oApp2.Quit
Set oApp2 = Nothing

> > I'm using automation to run some Excel vba, then using the
> > transferspreadsheet method in Access to import data from Excel. The
[quoted text clipped - 19 lines]
> really no longer in memory. Then let the code continue. If you find
> Excel in memory after that, I'll be very surprised.

Reply to this Message

Klatuu - 23 Oct 2006 14:13 GMT

I doubt your tests are giving you accurate information. I have never seen
TransferSpreadsheet cause this problem. It is very common with Automation if
the Excel object model is not handled very carefully.

How you instantiate your references to Excel objects and how you destroy
them is crucial. Each reference must be based on a previous reference. If
Access cannot determine which instance of Excel an object belongs to, it will
create an additional instance of Excel on it's own. When you close and quit
the instance you created, the instance Access created will remain resident.

Without going into lengthly code, here is a sequence of events that will
give you the best protection.

To establish an Excel Application object, first use the GetOjbect method.
If Excel is already open, it will use that instance. If not, it will throw
an error 429. If you get the error, then use the CreateObject method. Use a
boolean varialbe to keep track of whether excel was already open. If it
was, you don't want to do a Quit because the user had it open and will not be
happy if you close it on them.

Be sure that each Excel object you create is a child of the Application
object and that there is no ambiguity.

When you are done, be sure you close the workbook object. Test the boolean
variable to see whether or not you want to do a Quit on the application
object. Then, of course, set all your excel object references to nothing.

> I'm using automation to run some Excel vba, then using the
> transferspreadsheet method in Access to import data from Excel. The
[quoted text clipped - 4 lines]
> Is there a VBA way to programmatically remove excel from memory so the user
> doesn't have to go into the Windows Task Manager Processes tab?

Reply to this Message