How can I remove Excel from memory

[henniec]

Good day,

I need to remove Excel from memory before I can open Excel via VBA again. I have an Add-in Called NetOffice that extracts data from a process server.

Whenever I quit Excel and set Excel=nothing in code, there still remains an instance or even more of Excel in memory. If I try to re-run the code to extract data it causes an error. I must use Ctrl-Alt-Del to remove all instances of Excel via Task Manager.

Thank you for your time.

Hennie

 

[JerryKlmns]

You refer to an Excel object inplicitly somewhere in your code that leaves Excel object still alive even whene "I quit Excel and set Excel=nothing"

If you post the code, some hawk-eye TekTiper shall spot it.

 

[henniec]

Thank you for your reply. Here is the code and some reference is made why its done, where it may not seem logical

Sub OzoneData()

Dim objXL As Excel.Application
Dim objWkb As Object
Dim objSht As Object
Dim db As Database
Dim boolXl As Boolean
Dim cRows As Integer
Dim strQtr As String

Const conMAX_ROWS = 20000
Const conSHT_NAME1 = "Ozone"
Const conSHT_NAME2 = "NetOfficeOzone"

DoCmd.SetWarnings False
DoCmd.Echo False, ""

'######################################################################################

Const conWKB_NAME = "H:\Chem\DATA\XMLReport\MewsData.xls"

'Check if Excel is running
'I need to check if Excel is running and then Quit the aplication. If the create
'option is used to open an instance, the NetOffice add-in is not included and it is needed
'for this particular module. All other modules do not require the NetOffice add-in and
'can use the normal CreateObject function
'
If fIsAppRunning("Excel") Then
Set objXL = GetObject(, "Excel.Application")
boolXl = True
objXL.Quit
Set objXL = Nothing
End If

'I need to do the following three steps. If I rem them out Excel opens but without the
'NETOffice add-in and as such do not extract the data required.
Call Shell("Excel")
Excel.Application.Quit
Set objXL = Nothing
On Error Resume Next
Set objXL = GetObject(, "Excel.Application")
boolXl = True

Set db = CurrentDb

'#####################################################################

'Set the object variable to reference the file you want to see.
'MsgBox "Extracting data from Process Net", vbInformation

With objXL
.Visible = True 'False

Set objWkb = .Workbooks.Open(conWKB_NAME)

On Error Resume Next

Set objSht = objWkb.Worksheets(conSHT_NAME2)
objWkb.Worksheets(conSHT_NAME2).Activate

.Cells(1, 3).Value = Format(Forms![frmQueryDates]![BeginningDate], "mmm dd, yyyy") & " 08:00:00"
.Cells(2, 3).Value = Format(Forms![frmQueryDates]![EndingDate] + 1, "mmm dd, yyyy") & " 07:59:59"

objXL.Run "OzoneTransfer"

Set objSht = objWkb.Worksheets(conSHT_NAME1)
objWkb.Worksheets(conSHT_NAME1).Activate

With objSht

.Cells(1, 16).Value = "=COUNT(A1:A6500)"
cRows = .Cells(1, 16).Value

End With

DoCmd.TransferSpreadsheet acImport, 8, "sheet1", conWKB_NAME, True, "Ozone!A1" & cRows

DoCmd.OpenQuery "qryOzoneFinalReport", acViewNormal, acReadOnly

End With

objWkb.Save
DoCmd.SetWarnings True
DoCmd.Echo True, ""
Set objXL = Nothing
Set objWkb = Nothing
objXL.Quit
Set objSht = Nothing

End Sub

 

[bubba100]

objWkb.Save
DoCmd.SetWarnings True
DoCmd.Echo True, ""
Set objXL = Nothing
Set objWkb = Nothing
objXL.Quit
Set objSht = Nothing

you can't "quit" something already set to nothing. Move objXL.Quit above the Set objXL=Nothing.

 

[JerryKlmns]

Sub OzoneData()

Dim objXL As Object 'Excel.Application
Dim objWkb As Object
Dim objSht As Object
Dim db As DAO.Database
Dim boolXl As Boolean
Dim cRows As Integer
Dim strQtr As String

Const conWKB_NAME = "H:\Chem\DATA\XMLReport\MewsData.xls"  
Const conSHT_NAME1 = "Ozone"
Const conSHT_NAME2 = "NetOfficeOzone"
Const conMAX_ROWS = 20000
  
DoCmd.SetWarnings False
DoCmd.Echo False, ""

    '######################################################################################


'Check if Excel is running
'I need to check if Excel is running and then Quit the aplication.  If the create
    'option is used to open an instance, the NetOffice add-in is not included and it is needed
    'for this particular module.  All other modules do not require the NetOffice add-in and
    'can use the normal CreateObject function
    '
On Error Resume Next
Set objXL = GetObject(, "Excel.Application")
If Err = 0 Then
'Excel was running
   boolXl = True
   objXL.Quit
   Set objXL = Nothing
Else
'Excel was NOT running
   Set objXL = CreateObject("Excel.Application")
   boolXl = False
End If
'Turn on Error handler if you have one, at this point
    
    'I need to do the following three steps.  If I rem them out Excel opens but without the
    'NETOffice add-in and as such do not extract the data required.
'    Call Shell("Excel")
'    Excel.Application.Quit
'    Set objXL = Nothing
'    On Error Resume Next
'    Set objXL = GetObject(, "Excel.Application")
'    boolXl = True

    Set db = CurrentDb

    '#####################################################################

    'Set the object variable to reference the file you want to see.
    'MsgBox "Extracting data from Process Net", vbInformation

objXL.Visible = True   'False
Set objWkb = .Workbooks.Open(conWKB_NAME)
Set objSht = objWkb.Worksheets(conSHT_NAME2)
objSht.Cells(1, 3).Value = Format(Forms![frmQueryDates]![BeginningDate], "mmm dd, yyyy") & " 08:00:00"
objSht.Cells(2, 3).Value = Format(Forms![frmQueryDates]![EndingDate] + 1, "mmm dd, yyyy") & " 07:59:59"
objXL.Run "OzoneTransfer"
Set objSht = objWkb.Worksheets(conSHT_NAME1)
objSht.Cells(1, 16).Value = "=COUNT(A1:A6500)"
cRows = objSht.Cells(1, 16).Value
objWkb.Close, True
Set objSht = Nothing
Set objWkb = Nothing
objXL.Quit
Set objXL = Nothing

db.DoCmd.TransferSpreadsheet acImport, 8, "sheet1", conWKB_NAME, True, "Ozone!A1:D" & cRows
        
db.DoCmd.OpenQuery "qryOzoneFinalReport", acViewNormal, acReadOnly
    

DoCmd.SetWarnings True
DoCmd.Echo True, ""
  
End Sub

I see that you use DoCmd.SetWarnings False propably because you run action queries like Docmd.RunSQL You could stop changing SetWarnings if you execute your action queries with CurrentDB.Execute method

 

[henniec]

Good day,

Bubba100 - I have changed the sequence as you suggested but Excel still remains in memory. A seconf run still delivers the same errors.

JerryKlmns - As a matter of interest, why do you say not to change the Set Warnings with the CurrentDBExecute method?

Thank you.

Hennie

 

[JerryKlmns]

CurrentDB.Execute method doesn show that annoying message. Since you wrote the code to affect records there is no need to notify the user. Plus if you set it off and on and then again off but forget the on, it 's gonna be off for the rest of the time. Even when the user runs its own action query.

 

[henniec]

Thank you for the information.