Excel process will not end after Excel.application.quit in Access VBA

[SJohnE]

I have a routine that opens an Excel 2003 workbook, retrieve some custom properties and loades to memory for use, then closes the Excel workbook. When there is only one workbook to check there is not a problem because when the access appliation is closed it kills the specific Excel process.

The problem is that there may be multiple Excel files that my Access application needs to process one after the other without closing. I found some information that said, if I open the Excel file with its visibility property false then this happens, so I changed it, making it visible and it still leaves the process running after closing the workbook. Does anyone know how to get around this?

I thought about looping through the processes and killing all Excel.exe processes but the user may have workbooks open that are unrelated and I don't want to kill those processes, only the one specifically opened by my Access mdb. Help! See code below...

Note: some variables are defined as Public and populated else were. EPSET is the Excel workbook. I have also tried xl.quit

Function ExtractFile(ByVal workingFolder As String)

...

'Compare the version of the EPSET to the verson of the database
xlV$ = GetEPSETV(SourceFile$) 'calls the problematic routine
If UCase(xlV$) <> UCase(SWV$) Then
Err.Raise Number:=vbObjectError + 3000
End If

...
end function

Function GetEPSETV(SourceFile$)
'Created by: Steve Ellertson
'Created: 1/4/12
'Comments: This function gets the EPSET version and returns it to the calling
'function.
'_________________________________________________________________
Dim sfullpath$
Set xl = CreateObject("Excel.Application")
sfullpath$ = wrkdir$ & SourceFile$
GetEPSETV = Empty
With xl
.Workbooks.Open (sfullpath$)
.Visible = True
GetEPSETV = ActiveWorkbook.CustomDocumentProperties.Item("Version").Value
End With
Set xl = Nothing
Excel.Application.Quit 'This is the problem statement
End Function

S J E
If I am not learning, then I am coasting, if I am coasting I must be going down hill.

 

[kjv1611]

You're quitting a totally unrelated instance of Excel, going from your pasted code.

Instead of:

Set xl = Nothing
Excel.Application.Quit

You should have:

xl.Application.Quit
If xl Is Nothing Then Else Set xl = Nothing

The way I changed the set to nothing part is putting an IF statement on it, so it shouldn't throw an error if closing the application ended up erasing the variable as well. Anyway that seems (to me at least) to be a good check to put in place, in case anything happens along the way to change anything.

Give that a try and post back.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[PHV]

Replace this:
GetEPSETV = ActiveWorkbook.CustomDocumentProperties.Item("Version").Value
with this:
GetEPSETV = [!]xl.[/!]ActiveWorkbook.CustomDocumentProperties.Item("Version").Value

and this:
Set xl = Nothing
Excel.Application.Quit 'This is the problem statement
with this:
xl.Quit
Set xl = Nothing

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SJohnE]

Thanks all, I haven't tried your suggestions yet but will in short order. I just want to clarify. When I said that the quit statement was the problem statement I meant that it wasn't getting rid of the process in task mgr (memory leak). When the code runs for the subsequent XLS files to be processed an error is raised when I try to set the memory varialbe with the code: GetEPSETV = ActiveWorkbook.CustomDocumentProperties.Item("Version").Value

S J E
If I am not learning, then I am coasting, if I am coasting I must be going down hill.

 

[PHV]

Your code creates ghost Excel by using not full qualified object.
Did you try my suggestions ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

SJohnE,

Did you try any of the suggestions? Did you resolve the issue(s)?

As PHV mentioned, you need to specifically start your variables, and then specifically close THE SAME variables.

So, you want to do something like:

Private Sub MyProcedureName()
  Dim xlApp As New Excel.Application
  Dim wb As Excel.Workbook
  Dim ws As Excel.Worksheet

  Set wb = xlApp.OpenWorkbooks("MyWorkbookFilePath")
  Set ws = wb.Worksheets("WorksheetName")

  [green]'Do stuff with the worksheet and/or workbook[/green]
  wb.Save

  xlApp.Quit
  If ws Is Nothing Then Else Set ws = Nothing
  If wb Is Nothing Then Else Set wb = Nothing
  If xlApp Is Nothing Then Else Set xlApp = Nothing
End Sub

Now that code assumes you actually want to close Excel. If you don't want to close Excel, then don't use .Quit, just clear out the variables..

The way I do the variable clean-up now, personally, is checking first for its existence - if it is already gone, you don't need to clean it up. However, I have had a few occasions where it throws an error at that point. So if I'm in a hurry, I'll through in On Error Resume Next above the cleanup piece. Of course if I have time, then I go back and make sure to see if I need to clean up earlier, or whether it's just not necessary in the particular instance.

Anyway, post some feedback when you get a chance. Let us know if you've corrected it, and if so, then how.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57