| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Expunge cell from memory when late binding involved
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Expunge cell from memory when late binding involved
Try replacing
objExcel.Close with objExcel.Quit -- Hmm...they have the Internet on COMPUTERS now! "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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Expunge cell from memory when late binding involved
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Expunge cell from memory when late binding involved
"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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Expunge cell from memory when late binding involved
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. "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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Expunge cell from memory when late binding involved
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" wrote: 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Expunge cell from memory when late binding involved
what was wrong with
objExcel.Parent.Quit Set objExcel = Nothing as originally suggested. -- Regards, Tom Ogilvy "Perico" wrote in message ... 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" wrote: 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Expunge cell from memory when late binding involved
I get an error "Object Variable not set".
"Tom Ogilvy" wrote: what was wrong with objExcel.Parent.Quit Set objExcel = Nothing as originally suggested. -- Regards, Tom Ogilvy "Perico" wrote in message ... 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" wrote: 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Expunge cell from memory when late binding involved
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. "Tom Ogilvy" wrote: what was wrong with objExcel.Parent.Quit Set objExcel = Nothing as originally suggested. -- Regards, Tom Ogilvy "Perico" wrote in message ... 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" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| URGENT! Expunge Excel from Memory | Excel Programming | |||
| Late Binding | Excel Programming | |||
| Late Binding examples of binding excel application | Excel Programming | |||
| Late Binding | Excel Programming | |||
| EARLY binding or LATE binding ? | Excel Programming | |||
![Perico[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
