ms access - Excel.Application Object .Quit leaves EXCEL.EXE running

Question

I'm working within MS Access 2013 on Windows 10 and I'm simply trying to write a Sub that opens an Excel file on disk, changes the column formatting and some columns, saves and quits. The Sub runs and does as intended, but the problem is that even after the .Quit command, "EXCEL.EXE" keeps running and subsequent calls to that Sub will result in run-time errors. If I close Access after running the sub, "EXCEL.EXE" disappears on the Task Manager, but not if I do "Compact and Repair" the database.

I've done one other Sub that simply opens an Excel file on disk and alters te width of all columns to "auto-width", then closes, which works fine and doesn't leave "EXCEL.EXE" running.

What am I doing wrong? Is it possible in VBA to make sure that "EXCEL.EXE" correctly quits?

I tried a lot of different line order on Dims and Sets to Nothing, Workbook Closes, etc etc. Also, I searched here and on other sites on how to solve this, but after 2h the only suggestions I've seen for VBA is using something like ThisWorkbook.Saved = True, but I tried that before and after .Quit to no effect. Other than that, I only find solutions for VB.NET or other environments that I'm not using and now next to nothing of.

Thanks for reading this.

Code follows:

Sub changeXLcolumnFormatting()

Dim XL As Object
Set XL = CreateObject("Excel.Application")
XL.Visible = False
XL.DisplayAlerts = False
XL.Workbooks.Open "C:Users640344Desktop
awDataTest.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

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:estworkbook1.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:Users640344Desktop
awDataTest.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