Yes, I know that the problem is something new, but I have already researched a lot, but nothing has helped me so far. Also this is my first stackoverflow post, so if I have something wrong, please tell me. I want to import multiple Excel sheets one at a time when accessing through VBA code. My code still works, but I have a problem that the EXCEL.EXE instance always remains in the task manager, which causes problems when I want to import more than one file. I have already tried the solutions offered in the following threads:
Closing the excel.exe process
Excel VB.NET program leaves floating after EXCEL.EXE shutdown
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 starting from Access using VBA
Here is my import code, I only used VBA for several weeks, so please do not 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 Call AlternateKKS, so maybe there is something there that prevents Excel Excel from closing. There is quite some code in the function, so before I post it all, I would like to know what I should look for, which might cause the problem. What he does is basically read the sheet into an array, perform some modification of the data, and write it back to the sheet, "merged." I have already made sure that there is xlWB. or xlAppl. whenever I link to a sheet and I do not use the with statement as indicated in the msdn forum (link in the spoiler).
PS: , , "", - , ! ( ), , .
EDIT: ,
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
source = xlAppl.Transpose(source)