Excel.Application Object .Quit leaves EXCEL.EXE running

Question

I'm working in MS Access 2013 on Windows 10.

I'm trying to open an Excel file on disk, change the column formatting and some columns, save and quit.
The Sub runs, but after the .Quit command, "EXCEL.EXE" keeps running and subsequent calls to that Sub will result in run-time errors.
If I close Access, "EXCEL.EXE" disappears on the Task Manager, but not if I do "Compact and Repair" the database.

I've another Sub that opens an Excel file on disk and alters the width of all columns to "auto-width", then closes, which doesn't leave "EXCEL.EXE" running.

I tried different line orders on Dims and Sets to Nothing, Workbook Closes, etc.
I searched here and on other sites. The only suggestions for VBA is to use something like ThisWorkbook.Saved = True. I tried that before and after .Quit to no effect.
Other than that, I only find solutions for VB.NET or other environments.

Sub changeXLcolumnFormatting()

Dim XL As Object
Set XL = CreateObject("Excel.Application")
XL.Visible = False
XL.DisplayAlerts = False
XL.Workbooks.Open "C:\Users\640344\Desktop\rawDataTest.XLSX"
Dim sht As Worksheet

With XL
    Set sht = ActiveWorkbook.Worksheets(1)

    Dim rng As Range
    Dim i As Integer, j As Integer

    field_names = Split("datasistema|Data de Registo|Data Registo CMVM", "|")
    sht.Select
    end_of_table = sht.UsedRange.Columns.Count

    For j = 0 To UBound(field_names)
        For i = 1 To end_of_table
            Set rng = sht.Cells(1, i)
            If InStr(rng.Text, field_names(j)) > 0 Then
                sht.Columns(i).NumberFormat = "yyyy-mm-dd  HH:MM:ss"
            End If
        Next i
    Next j
End With

Set rng = Nothing
Set sht = Nothing
XL.ActiveWorkbook.Close (True)

XL.Quit
Set XL = Nothing

End Sub

Kella

Are you sure that before starting the macro you do not have any other Excel running? – Bremser

You can try by adding Application.Quit in stead of XL.Quit just to see how it would work. – Bremser

Also try to save changed workbook explicitly before closing – Austrasia

Put the command Application.Quit after your close command – Claw

@Vityata: Yes, Excel.exe was not running nor showing in Task Manager prior to running the Sub in Access. Using Application.Quit quits MS Access, which is not desired. Though, that does make "EXCEL.EXE" disappear from the Task Manager, which makes sense since it's Access that's not closing Excel properly because my Sub isn't right. – Kella

@SergeyS. Using "XL.ActiveWorkbook.Save" explicitly didn't solve my problem. – Kella

Related: #55423720 – Winged

Answer 1

Declare and use a specific Workbook object - as you do for Worksheet and Range, like this:

    Dim xls     As Excel.Application
    Dim wkb     As Excel.Workbook
    Dim wks     As Excel.Worksheet
    Dim rng     As Range
    
    Set xls = New Excel.Application
    Set wkb = xls.Workbooks.Open("c:\test\workbook1.xlsx")
    Set wks = wkb.Worksheets(1)
    Set rng = wks.Range(<something>)
    
    ' Do stuff.
    wks.Name = "My New Name"
    With rng
         ' Do more.
    End With

    wkb.Close True
    
    Set rng = Nothing
    Set wks = Nothing
    Set wkb = Nothing
    
    xls.Quit
    
    Set xls = Nothing

Also, don't use Select, that's for visible use only. Define ranges instead.

---

Cinetyk's EDIT:

Using @Gustav 's indications, the code that does what I wanted and solves the problem is:

Sub changeXLcolumnFormatting()

Dim XL As Excel.Application
Dim sht As Excel.Worksheet
Dim wkb As Excel.Workbook
Dim rng As Range

Set XL = New Excel.Application
XL.Visible = False
XL.DisplayAlerts = False

Set wkb = XL.Workbooks.Open("C:\Users\640344\Desktop\rawDataTest.XLSX")
Set sht = wkb.Worksheets(1)

Dim i As Integer, j As Integer

field_names = Split("datasistema|Data de Registo|Data Registo CMVM", "|")
end_of_table = sht.UsedRange.Columns.Count

For j = 0 To UBound(field_names)
    For i = 1 To end_of_table
        Set rng = sht.Cells(1, i)
        If InStr(rng.Text, field_names(j)) > 0 Then
            sht.Columns(i).NumberFormat = "yyyy-mm-dd  HH:MM:ss"
        End If
    Next i
Next j

wkb.Close (True)
Set rng = Nothing
Set sht = Nothing

XL.Quit
Set XL = Nothing

End Sub

Paluas

Gustav, that did it. Thanks a lot. Looking at your answer, now, it makes sense: declaring objects explicity makes it so I can "close" and Set them to nothing explicity. I was leaving using some variables defined implicity and thus those weren't being "closed", probably the links to them in memory remained. I'm learning VBA as I go, this was a good lesson, thanks a lot again. Also thanks for everyone that took the time to read and comment. – Kella

Upvoted the comment instead of the answer because it explains the cause while the answer doesn't. Without explaining the error mechanism, the answer is useless because you'll never know if you eliminated the error and which of the lines did it. – Bricker

This line "Set xls = New Excel.Application" should not be overlooked. By opening a new instance, the code can safely shut down Excel without interfering with other Excel instances the user may be visibly interacting with. – Soukup

Answer 2

Here is a fancy way to solve that problem - using with new Excel.Application:

Option Compare Database
Option Explicit

Public Sub TestMe()

    Dim wkb     As Object
    Dim wks     As Object

    With New Excel.Application

        Set wkb = .Workbooks.Open("C:\Users\vityata\Desktop\myTest.xlsx")
        Set wks = wkb.Worksheets(1)

        wkb.Close True

        Set wks = Nothing
        Set wkb = Nothing
        .Quit

    End With

End Sub

In C# this is a standard with Using, in VBA very few people use it - I have never seen it production code.

---

Cinetyk's EDIT:

Using Vityata's indications, the code that works as I intended is:

Option Compare Database
Option Explicit

Public Sub changeXLcolumnFormattingV2()

Dim sht As Object
Dim wkb As Object

With New Excel.Application
    .Visible = False
    .DisplayAlerts = False

    Set wkb = .Workbooks.Open("C:\Users\640344\Desktop\rawDataTESTING.XLSX")
    Set wks = wkb.Worksheets(1)

    field_names = Split("datasistema|Data de Registo|Data Registo CMVM", "|")
    end_of_table = wks.UsedRange.Columns.Count

    For j = 0 To UBound(field_names)
        For i = 1 To end_of_table
            Set rng = wks.Cells(1, i)
            If InStr(rng.Text, field_names(j)) > 0 Then
                wks.Columns(i).NumberFormat = "yyyy-mm-dd  HH:MM:ss"
            End If
        Next i
    Next j

    wkb.Close True

    Set wks = Nothing
    Set wkb = Nothing
    .Quit

End With

End Sub

Bremser

welcome, @Cinetyk. Unfortunately, it does not work exactly as in C#, where the Using() would take care to fully destroy and release the object, but it is still nice. – Bremser

But on running it MS Access, excel.exe doesn't remain in background after using that code, so it works for me. – Kella

@Kella - Because you have the .Quit in the code. But in the best case scenario, if VBA was a bit better, it should have closed by itself, without needing the .Quit. – Bremser

Answer 3

Your sht variable assignment is missing the member accessor operator “.” in front of ActiveWorkbook.Worksheets(1). It needs to be written as follows: Set sht = .ActiveWorkbook.Worksheets(1).

Norge