Closing Excel instance in memory

[aziz rasul]

I have the following code, which closes an MS Excel file which is open:

Public Sub CloseExcelFile(strExcelFile As String)
    
    Dim objExcelApp As Object
    
    Set objExcelApp = GetObject(strExcelFile)
    
    With objExcelApp
        .Application.Visible = False
        .Close SaveChanges:=False
    End With

    Set objExcelApp = Nothing
    
End Sub

The code works OK but leaves the Excel instance of the file still in memory. Is there any line of code I can use to get rid of this instance?

 

[aziz rasul]

I should have said

The code works OK but leaves the Excel application instance still in memory. Is there any line of code I can use to get rid of this instance?

 

[Mr. B]

Give this a try:

Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objXLSheet As Excel.Worksheet

'open Excel
Set objXLApp = New Excel.Application

'The next line can be uncommented if you want to see the spreadsheet
'objXLApp.visible = True


'open the spreadsheet
Set objXLBook = objXLApp.Workbooks.Open(strExcelFile)

'if you need to work with a specific sheet use the code below to 
Set objXLSheet = objXLBook.Sheets("NameOfSheetHere")

'Close the worksheet and the applicaiton
'Set the parameter to "True" to save changes, "False" to not save
CloseExcel True


Function CloseExcel(SaveChanges As Boolean)
On Error GoTo Err_CloseExcel

objXLApp.Application.DisplayAlerts = False
If SaveChanges = True Then
    objXLBook.Save
End If
objXLBook.Close
objXLApp.Application.DisplayAlerts = True
objXLApp.Application.Quit

Exit_CloseExcel:
    Set objXLSheet = Nothing
    Set objXLBook = Nothing
    Set objXLApp = Nothing
    Exit Function

Err_CloseExcel:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_CloseExcel
    
End Function

Be sure that you use the specific declared objects when working with the type of object. Failure to use the reference to the declared objects will cause the instance of Excel to be left in memory as you have already experiences.

 

[aziz rasul]

I get the error 'The object invoked has disconnected from its clients' on the line

objXLApp.Application.Quit

 

[Mr. B]

Could you post all of your code? Just seeing the line where the error is stopping may not tell the complete story.

 

[aziz rasul]

Note my Excel file is already open and I'm trying to delete it. In my main code I have

Kill (Me.lblOutputPath.Caption)

Other code

Error:
    If Err.Number = 5 Then
        Resume Next
    ElseIf Err.Number = 70 Then
        Call CloseExcelFile(Me.lblOutputPath.Caption)
        Kill (Me.lblOutputPath.Caption)
        Resume Next
    End If

In a separate module I have the code which is to to close the opened file

Public Sub CloseExcelFile(strExcelFile As String)
    
    Dim objExcelApp As Object

    Set objExcelApp = GetObject(strExcelFile)

    With objExcelApp
        .Application.Visible = True
        .Close SaveChanges:=False
        .Application.Quit
    End With

    Set objExcelApp = Nothing

End Sub

 

[Mr. B]

Sorry, if I misunderstood your question. The reason that I posted the code that I posted is because the "leaves the Excel instance of the file still in memory" issue is caused by using code that does not specifically refer to a defined object. I was just attempting to provide code that I have used that does use a reference to a defined object to address each action that I need to take. This will then allow me to not only close and save the file but it will also allow me to close the the specific instance of Excel and set it and the other objects to "nothing".

This will eliminate the orphan instance of Excel still be in open.

Again, sorry if you felt that my post was not helpful.

To address the issue; if you want to delete the file, you will have to be sure you have first closed the file and then you will be able to delete it. This does go back to the very reason for making sure you use specifically defined objects in your code.

 

[aziz rasul]

No apologies necessary. I may not have been very clear myself.

Although the empty Excel application is left in memory, it hasn't caused a problemn thus far. I wanted to get rid of it just in case it did cause a problem in the future.