EXCEL.EXE won't close

Question

Yes I know the problem is anything but new and I researched already A LOT but nothing helped me so far. Also this is my first stackoverflow post so if I do something wrong, please tell me. What I want to do is import multiple Excel sheets one at a time in access via VBA code. My code is working so far but I have the problem that there always remains an EXCEL.EXE instance in the task manager, causing problems when I want to import more than 1 file. I already tried the solutions proposed in following threads:

Closing Excel.exe process

VB.NET Excel Program Leaves EXCEL.EXE floating after completion

https://social.msdn.microsoft.com/Forums/en-US/908ba72a-3293-4eb9-b80e-fd2e6e78e185/vba-close-excel-problem?forum=accessdev

Remove Excel task from Task manager after running from Access using VBA

Here is my import code, I've only been using VBA for a few weeks, so please don't look too closely at it.

Public Function importExcelFiles()


Dim tablename As String
Dim spreadsheetType As String
Dim xlAppl As Excel.Application
Dim xlWB As Excel.Workbook
' **** function imports the excel file and copies data into target table  *********

' choose the file to import
path = OpenFile("Select file for import", "Excel-Files" & Chr$(0) & "*.xls; *.xlsx; *.xlsm", "Excel-Files", CurrentProject.path)

If Not IsNull(path) And Not path = "" And Not path = " " Then

    Set xlAppl = CreateObject("excel.application")
    Set xlWB = xlAppl.Workbooks.Open(path)
    
   
    spreadsheetType = acSpreadsheetTypeExcel9
    
    Call AlternateKKS("KKS_DB", xlAppl, xlWB)
    tablename = "KKS_DB"
    DoCmd.TransferSpreadsheet acImport, spreadsheetType, tablename, path, false, "merged!" 'import to excel'
            

    
    DoCmd.SetWarnings False
    xlAppl.DisplayAlerts = False
    xlWB.Close acSaveNo
    xlAppl.Quit
    
    Set xlWB = Nothing
    Set xlAppl = Nothing

    
End If


End Function 

I noticed that it works when I uncomment the Call AlternateKKS so probably there is something in there that keeps the Excel.exe from closing. There is quite some code in the function so before I post all of this I would like to know what I should be looking for that could cause the problem. What it does is basically read the sheet into an array, do some modification to the data and write it back on the sheet "merged". I already made sure that there is a xlWB. or xlAppl. whenever I refer to a sheet and I do not use the with statement as mentioned on the msdn forum (link in spoiler).

PS: On another note, the sheet I want to import is called "merged" but for some reason it only works if I put the ! at the end (a colleague told me to do it) but we have no idea why.

EDIT: Here is all the code that refers directly to the application or a sheet

Dim wsSource As Worksheet
Dim wsDestination As Worksheet

Public Function ReadIntoArray(name As String, ByRef xlWB As Excel.Workbook) As Variant


'    Dim ret As String
g_arrayrange = GetLastCell(name, xlWB)

Set wsSource = xlWB.Sheets(name)

Call Sort(wsSource, xlWB)

ReadIntoArray = wsSource.range("A1:" & g_arrayrange).Value


End Function

Sub WriteBackArray(data() As Variant, destination As String, ByRef xlWB As Excel.Workbook)


Set wsDestination = xlWB.Sheets(destination)

wsDestination.UsedRange.ClearContents

wsDestination.range("A1").resize(UBound(data, 1), UBound(data, 2)) = data


End Sub

Function GetLastCell(name As String, ByRef xlWB As Excel.Workbook) As String 'return coordinates of last cell with data'

Dim lrw As Long
Dim lcol As Long
Dim lastCell As String
Dim rng As range
Dim sheet As Worksheet
Dim col As String

Set sheet = xlWB.Sheets(name)
Set rng = xlWB.Sheets(name).Cells
'get last row '

lrw = rng.Find(What:="*", _
                After:=rng.Cells(1), _
                Lookat:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).row


'get last column'
lcol = rng.Find(What:="*", _
                After:=rng.Cells(1), _
                Lookat:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Column

g_lrw = lrw

'change to string for further use as range'
lastCell = GetCellAsString(lcol, lrw)

GetLastCell = lastCell


End Function

And in another function

source = xlAppl.Transpose(source)

Rest of the code only manipulates arrays

Answer 1

Global variables (Dimed on module level) keep their values indefinitely, object variables keep their references. So a global variable like

Dim wsSource As Worksheet

will keep its reference to the Excel object, and thus prevent EXCEL.EXE from shutting down.

Solutions:

1. Set wsSource = Nothing once it is no longer used. This can be tricky - you must handle every possible path through your program logic.

2. Use local variables instead (Dimed on function level). Pass them as parameters to other functions where necessary. This is a safer way.

Answer 2

Since there seems to be no function that lets me mark Andre's comment as answer:

Then you must do Set wsSource = Nothing etc. once they are no longer used, or they will hold their reference indefinitely. Local variables (that are passed as parameters to other functions where necessary) are safer in this regard

Both of your suggested solutions worked! Set wsSource = Nothing when it was global as well as rewriting the code so that wsSource and wsDestination were local variables made the EXCEL.EXE close properly.

Thank you!

@Andre Feel free to post your comment as answer and I'll mark it as solution