excel - MS Access VBA EXCEL.EXE 在 .quit 之后没有终止并且 = 没有

Question

我正在运行打开用户提供的电子表格的代码。它只是引入了第一列，不包括标题。运行代码一次按预期工作，除了它使 EXCEL.EXE 实例在最后保持打开状态。我已经阅读了几个有类似问题的问题，所有答案都围绕着找到任何没有退出/关闭然后设置为空的对象。我对代码中的每个对象都执行此操作，甚至进行错误检查，如果它没有完成，则会捕获并退出并清除对象。在 EXCEL.EXE 未关闭后第二次运行代码时，.Cells(Rows.Count, 1).End(xlUp).Row任何人都知道为什么会抛出“（1004）应用程序定义或对象定义错误”？任何帮助，将不胜感激

Private Sub SplitImports()
Dim StringVar As Variant
Dim strLn As String

'Asks user for Filepath
StringVar = InputBox("Please enter the file path for your list", "Import", "H:\FNMA_WFDC")

'Ends Function if no input or cancel is detected
MsgBox (StringVar)
If (StringVar = vbNullString) Then
    MsgBox ("No input, Please try again")
    Quittracker = True
    Exit Sub
End If

'Scrubs outer quotes if present
StringVar = Replace(StringVar, Chr(34), "", 1, 2)

'Creates the object to check the file
Dim FSO As Object
Set FSO = CreateObject("Scripting.Filesystemobject")
MsgBox ("Got Passed the FSO Object")

'Checks that our file exists, exits if not
If (Not FSO.FileExists(StringVar)) Then
    MsgBox ("File does not exist, try again")
    Quittracker = True
    Exit Sub
End If

Set FSO = Nothing

Dim xlApp As Object 'Excel.Application
Dim xlWrk As Workbook 'Excel.Workbook
Dim i As Long
Set xlApp = New Excel.Application
MsgBox ("Dimmed the excel objects")
xlApp.Visible = False

On Error GoTo ErrorTrap

Set xlWrk = xlApp.Workbooks.Open(StringVar) 'opens the excel file for processing
MsgBox ("objects are set")

With xlWrk.Worksheets("Sheet1")

    .Columns("A").NumberFormat = "@"
    MsgBox (.Cells(Rows.Count, 1).End(xlUp).Row)

    'walks through the excel sheet to the end and inserts the lines below the headerline into the database
    For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
        DoCmd.RunSQL "Insert Into Split_List(Criteria) values('" & .Cells(i, 1).Text & "')"
    Next i

End With
MsgBox ("About to Clear and close the objects")

'closes the workbook and quits the application
xlWrk.Saved = True
xlWrk.Close
Set xlWrk = Nothing
xlApp.Quit
Set xlApp = Nothing

MsgBox ("End of the import sub")
Exit Sub

ErrorTrap:
    xlWrk.Saved = True
    xlWrk.Close
    Set xlWrk = Nothing
    xlApp.Quit
    Set xlApp = Nothing
    MsgBox ("(" & Err.Number & ") " & Err.Description    
    Quittracker = True
    Exit Sub

标签： excelvbams-access