Expunge cell from memory when late binding involved

[Perico]

Am running Excel vba, situated in an Excel module, from Access 2k. I've
followed all the rules of automation in order to expunge Excel from memory
once the Access routine completes: fully qualified references; no
"selection", "activecell" usage; no Excel reference in Access module
References dialogue box; reverse order object destruction.

The techniques recommended by Mssrs. Cone and Ogilvy worked well in my
export routine where I'm creating an Excel csv file.

But having trouble with my import routine where I grab a pre-exisiting Excel
file using GetObject(). Excel still shows in memory. How can I prevent
Excel from remaining in memory after the Access routine completes when using
late binding like this? Should I be using the "New" keywork when dimming or
setting the object variables? Here is the relevant part of the code:

Private Sub cmdImport_Click()

Dim objExcel As Object

On Error Resume Next
Set objExcel = GetObject("C:\Projects\MyFile.xls", "Excel.Sheet")

'check if Excel already running:
On Error GoTo 0
If objExcel Is Nothing Then
MsgBox "No File Sheet Exists!", vbInformation, "Inspect Excel"
End If

objExcel.Application.Visible = True
objExcel.Application.Windows(vFile).Visible = True

'cycleRes etc is name of Excel sub I run from Access:
If vType = "Res" Then
objExcel.Application.Run "cycleRes" 'in Excel module
ElseIf vType = "Nres" Then
objExcel.Application.Run "cycleNRes"
End If

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, vTableNameData,
vFullPath, True, vRangeData

If vType = "Res" Then
objExcel.Application.Run "REVERSEcycleRes" 'Excel vba run from Access
ElseIf vType = "Nres" Then
objExcel.Application.Run "REVERSEcycleNRes"
End If

objExcel.Close
Set objExcel = Nothing

End Sub

 

[MDW]

Try replacing

objExcel.Close

with

objExcel.Quit

 

[Tom Ogilvy]

Quit is the command to close excel

objExcel.Quit
Set objExcel = Nothing

 

[Perico]

"I get an object doesn't support this property" error when changing to
objExcel.Quit. If I change to objExcel.Application.Quit, I get a type
mismatch error popup over the spreadsheet, but the am still able to F8 thru
the code in Access. Strange. But Excel remains in memory. So still no
cigar.

PLEASE HELP. Have been grappling for 2 days with this issue in a
GetObject() context where there is a pre-existing Excel file.

 

[Tom Ogilvy]

Misread what ObjExcel is. It appears to be workbook

objExcel.Parent.Quit
Set objExcel = Nothing


You can test what object it is with

msgbox typename(objExcel)

 

[Perico]

Please note the line, Set objExcel = GetObject"C:\Projects\MyFile.xls",
"Excel.Sheet")
specifies Sheet, which is the only way I could get the Excel macros to run.

 

[Perico]

I found out that notwithstanding the
Get objExcel = GetObject(vFullPath,"Excel.Sheet") line, the msgbox shows
objExcel is a workbook.

So I tried msgbox objExcel.parent and it shows as "Application". But when I
ran the objExcel.parent.quit and set objExcel.parent = nothing I get an error
"Object Variable not set". So I tried to set objExcel.parent =
Excel.Application, which I don't think you can do in late binding, and sure
enough got another error. Then I set objExcel.parent = Application, didn't
get an error on that line, but did get an error when I tried
objExcel.parent.quit, set objExcel.parent = nothing, as well as
objExcel.parent.close.

You would think that even with late binding you could get at the Application
level to remove it from memory. I don't thing the fact I'm running the Excel
macro (from Access) in the line:

objExcel.Application.Run "cycleRes"

is keeping anything in memory. When the Access routine is done, I manually
close without saving the Excel file. I also saved the Excel file in one test
but still Excel remained in memory. There must be a way to get at that
objExcel (workbook) parent. That must be the key to this.

 

[Tom Ogilvy]

what was wrong with

objExcel.Parent.Quit
Set objExcel = Nothing

as originally suggested.

 

[Perico]

I get an error "Object Variable not set".

what was wrong with

objExcel.Parent.Quit
Set objExcel = Nothing

as originally suggested.

 

[Perico]

The cause of Excel remaining in memory has finally been discovered. I
commented out the transferspreadsheet method lines, and no residual Excel in
memory resulted. That was the culprit. So at least we now know that it was
not due to running Excel vba from Access.