Expunge cell from memory when late binding involved
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)
--
Regards,
Tom Ogilvy
"Perico" wrote in message
...
"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" wrote:
Quit is the command to close excel
objExcel.Quit
Set objExcel = Nothing
--
Regards,
Tom Ogilvy
"Perico" wrote:
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
|