Excel object still in memory after Access app runs

[rlntemp-gng]

RE: Access 2003/Excel 2003
Problem:
After I close the Access application completely, I go out to the Task
Manager and there is an Excel.exe object still sitting out there.

My Access application creates an Excel file (MyTestFile.XLS ) just fine
per the code submitted here. This code closes the Excel file created
and close the Excel application, leaving the Access app open. Then I
close Access. After Access is closed, I go to the task mgr and an
Excel object is still out there.

Question:
How (using VBA) can I check the Task Manager of the Workstation, check
to see if an Excel object is there, and if it is there, flush it from
the Task Manager?
When this output file is created, it needs to be saved regardless.
Can this be done?
This code is from the subroutine that loops through a recordset to
create the Excel file.
<begin code>
'Dim the Excel objects smallest to largest...
Dim objXL As Excel.Applicati on
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet

Set objXL = New Excel.Applicati on

With objXL
Set objWkb = .Workbooks.Open (conWKB_NAME)
On Error Resume Next
Set objSht = objWkb.Workshee ts(conSHT_NAME)
If Not Err.Number = 0 Then
'MsgBox conSHT_NAME & " was not found in ExcelFile: " &
conWKB_NAME & vbCrLf & _
"so data will be pasted in a new sheet labeled: " &
conSHT_NAME
Set objSht = objWkb.Workshee ts.Add
objSht.Name = conSHT_NAME
End If
Err.Clear
On Error GoTo 0

> ....Do Excel stuff here

to create the output .XLS file.... <<

'Close Excel objects in reverse order they were declared...
'Closing Sheet, Workbook, Application...
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
<end code>

thnx....

[Matt]

On Mar 21, 3:48 pm, [e-mail] wrote:

RE: Access 2003/Excel 2003
Problem:
After I close the Access application completely, I go out to the Task
Manager and there is an Excel.exe object still sitting out there.

My Access application creates an Excel file (MyTestFile.XLS ) just fine
per the code submitted here. This code closes the Excel file created
and close the Excel application, leaving the Access app open. Then I
close Access. After Access is closed, I go to the task mgr and an
Excel object is still out there.

Question:
How (using VBA) can I check the Task Manager of the Workstation, check
to see if an Excel object is there, and if it is there, flush it from
the Task Manager?
When this output file is created, it needs to be saved regardless.
Can this be done?

This code is from the subroutine that loops through a recordset to
create the Excel file.
<begin code>
'Dim the Excel objects smallest to largest...
Dim objXL As Excel.Applicati on
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet

Set objXL = New Excel.Applicati on

With objXL
Set objWkb = .Workbooks.Open (conWKB_NAME)
On Error Resume Next
Set objSht = objWkb.Workshee ts(conSHT_NAME)
If Not Err.Number = 0 Then
'MsgBox conSHT_NAME & " was not found in ExcelFile: " &
conWKB_NAME & vbCrLf & _
"so data will be pasted in a new sheet labeled: " &
conSHT_NAME
Set objSht = objWkb.Workshee ts.Add
objSht.Name = conSHT_NAME
End If
Err.Clear
On Error GoTo 0

> ....Do Excel stuff here

to create the output .XLS file.... <<

'Close Excel objects in reverse order they were declared...
'Closing Sheet, Workbook, Application...
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
<end code>

thnx....

throw the following at the end (Where you close the sheet):

objWkb.close
objXL.quit

[AlterEgo]

rlntemp,

You never close the workbook: objWkb.Close

You only destroyed the reference to it.

-- Bill

<rl*********@ya hoo.comwrote in message
news:11******** **************@ l77g2000hsb.goo glegroups.com.. .

RE: Access 2003/Excel 2003
Problem:
After I close the Access application completely, I go out to the Task
Manager and there is an Excel.exe object still sitting out there.

My Access application creates an Excel file (MyTestFile.XLS ) just fine
per the code submitted here. This code closes the Excel file created
and close the Excel application, leaving the Access app open. Then I
close Access. After Access is closed, I go to the task mgr and an
Excel object is still out there.

Question:
How (using VBA) can I check the Task Manager of the Workstation, check
to see if an Excel object is there, and if it is there, flush it from
the Task Manager?
When this output file is created, it needs to be saved regardless.
Can this be done?
This code is from the subroutine that loops through a recordset to
create the Excel file.
<begin code>
'Dim the Excel objects smallest to largest...
Dim objXL As Excel.Applicati on
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet

Set objXL = New Excel.Applicati on

With objXL
Set objWkb = .Workbooks.Open (conWKB_NAME)
On Error Resume Next
Set objSht = objWkb.Workshee ts(conSHT_NAME)
If Not Err.Number = 0 Then
'MsgBox conSHT_NAME & " was not found in ExcelFile: " &
conWKB_NAME & vbCrLf & _
"so data will be pasted in a new sheet labeled: " &
conSHT_NAME
Set objSht = objWkb.Workshee ts.Add
objSht.Name = conSHT_NAME
End If
Err.Clear
On Error GoTo 0

> ....Do Excel stuff here

to create the output .XLS file.... <<

'Close Excel objects in reverse order they were declared...
'Closing Sheet, Workbook, Application...
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
<end code>

thnx....

[rlntemp-gng]

>>objWkb.clos e
objXL.quit<<

Matt,
This worked! Thank you for this suggestion.
When my application closes, the Excel reference in Task Manager is
destroyed as it should be.

I inherited all this code, so am stumped on a couple more items
here....

When ICCDataOut.XLS exists:
A new sheet is added to this .XLS file (sheet2, sheet3, sheet4, etc.)
This is ok, but I might look later into creating a custom string for a
particular run and placing the date-time on the sheet tab name.

I probably need to interrogate to see if ICCDataOut.XLS exists first.
(Are there efficient ways to do this in VBA?)
If it exists, check to see if the there is a sheet called
"ICCDataShe et". If there is not, then use "ICCDataShe et" as the sheet
name.
If the output file does not exist, I need to open the Excel object
with a new file, run code to load it up, then save the sheet and file
upon closing the Access form.

Currently, when the output .XLS file does not exist, this line errors:
Set objWkb = .Workbooks.Open (conWKB_NAME)
The error is "1004-Application defined or object defined error"
Is there a way to bypass this error so it would open a new sheet
anyway in the event that this file did not exist prior to creation?
Here is the code I have thus far:
Const conSHT_NAME = "ICCDataShe et"
Const conWKB_NAME = "C:\ICC\ICCData Out.XLS"

Set objXL = New Excel.Applicati on

With objXL
Set objWkb = .Workbooks.Open (conWKB_NAME)
On Error Resume Next
Set objSht = objWkb.Workshee ts(conSHT_NAME)
If Not Err.Number = 0 Then
' conSHT_NAME was not found in Excel File - conWKB_NAME
' data will be pasted in a new sheet labeled: " &
conSHT_NAME
Set objSht = objWkb.Workshee ts.Add
objSht.Name = conSHT_NAME
DebugPrint "Excel File is: " & conWKB_NAME & "/ Added
New sheet: " & objSht.Name
Else
DebugPrint "Excel File is: " & conWKB_NAME & "/ Using
Existing sheet: " & objSht.Name
End If

Err.Clear
On Error GoTo 0 'not sure if this is actually needed here (?)

End With
thnx....