VBA to Close Excel Not Closing Excel

[Frothingslosh]

Okay, this one is driving me bananas.

I have a driver procedure that is supposed to call a number of sub-procedures, then close the spreadsheet I was importing from and quit Excel.

Here is the code. Please excuse the mess, it's still under development, so this isn't my final code by any means.

Private Sub cmdImportCase_Click()
 
Dim CaseData As Collection
Dim ValCheck As clsFieldData
 
On Error GoTo ErrHandler

    If Not CaseData Is Nothing Then Set CaseData = Nothing
    Set CaseData = New Collection
    HeaderRow = GetDBOption("ArchiveImport_HeaderRow", otNumber, dbeBackEnd)
    
    CurrentDb.Execute "DELETE * FROM tblArchiveCaseStaging", dbSeeChanges + dbFailOnError
    CurrentDb.Execute "DELETE * FROM tblArchiveClaimStaging", dbSeeChanges + dbFailOnError
 
    [COLOR=seagreen]'Open the source file and assign values to xlApp, xlWorkBook, and xlWorkSheet.[/COLOR]
    If OpenImportFile() Then
        [COLOR=seagreen]'Verify the format of the source file.[/COLOR]
        If (VerifyCaseRows() And VerifyColumnNames()) Then
            [COLOR=seagreen]'The data file has been found and opened.  Import and stage the CASE information.[/COLOR]
            If ImportHeader(CaseData) Then
                If ImportClaimData() Then
                    MsgBox "Import Complete."
                End If
            End If
        Else
            Beep
            ShowMsg "The source file is formatted incorrectly." & vbCrLf & vbCrLf & _
                    "If you feel you have received this message in error, please contact PIU App Support.", _
                    "INVALID FILE FORMAT", _
                    "VALIDATION FAILURE", _
                    mbCritical
        End If
    End If
    
    Me.RecordSource = "tblArchiveCaseStaging"
    
    Call ToggleVisibility(True)
    If Me.txtFileNumber.Visible Then Me.txtFileNumber.SetFocus
    
ProcExit:
    On Error Resume Next
    If Not xlWorkSheet Is Nothing Then Set xlWorkSheet = Nothing
    If Not xlWorkBook Is Nothing Then
        xlWorkBook.Close SaveChanges:=False
        Set xlWorkBook = Nothing
    End If
    If Not xlApp Is Nothing Then
        xlApp.Quit
        Set xlApp = Nothing
    End If
    Exit Sub
    
ErrHandler:
    Trap.Handle Me.Name, "cmdImportCase_Click"
    Resume ProcExit
    
End Sub

The Excel variable declarations are module-level:

[COLOR=seagreen]'Early bound - FOR DEVELOPMENT ONLY[/COLOR]
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
 
[COLOR=seagreen]'Late bound - FOR RELEASE VERSIONS
'Dim xlApp As Object
'Dim xlWorkBook As Object
'Dim xlWorkSheet As Object[/COLOR]

The objects themselves are assigned in OpenImportFile. There is some extra stuff, but the actual assignments are pretty standard:

       [COLOR=seagreen]'Open a new session of Excel and then open the workbook.[/COLOR]
        Set xlApp = CreateObject("Excel.Application")
                
       [COLOR=seagreen]'Open the indicated workbook and worksheet[/COLOR]
        Set xlWorkBook = xlApp.Workbooks.Open(FileName:=SourceFilePath)
        Set xlWorkSheet = xlWorkBook.Sheets("INPUT FILE")

(I'm deliberately opening a new instance of Excel instead of using any existing one specifically so I can hide the workbook and close it when I'm done, since this is a straight validation and data import.)

So here is the situation:

While the cleanup in ProcExit works perfectly if the user has no other instances of Excel open, if they already have any open, then this code opens the spreadsheet being imported in a new, visible instance of Excel, then leaves it open rather than closing it out. Stepping through the cleanup code in ProcExit shows that every line is being executed; they just aren't DOING anything.

Any thoughts on why this is acting like this or how to fix that would be most appreciated.

 

[Frothingslosh]

Also, I've checked with it Resume Next turned off, and no errors are being suppressed by it.

 

[JHB]

I would also comment that out:

On Error GoTo ErrHandler

What happen if you only have xlApp.Quit in ProcExit:.

 

[Frothingslosh]

It's not throwing any errors at all, even after removing all error handling. I've stepped through the entire procedure, and everything works, except for closing out. It just outright ignores the Workbook.Close and Excel.Quit commands. Doesn't even throw any errors on them - it just process them and does nothing.

It appears to be something about saving changes - I've changed the workbook assignment to include ReadOnly:=True and the issue went away.

I don't know what precisely was causing it or how to fix it if I ever need to change a workbook and then import something (I tested with Workbook.Close SaveChanges:=True and the behavior never changed), but for now, I'll take what I can get. I would love to know WHY it was showing the workbook and refusing to close out, though, even if I DID find a work-around.

Oh, nearly forgot:

What happen if you only have xlApp.Quit in ProcExit:.

Absolutely no changes. The workbook appears, Excel stays open, and no errors are thrown.

 

[Frothingslosh]

Interestingly, unless I use that ReadOnly:=True, about 5 seconds after the procedure terminates, a pop-up appears in Excel saying 'ArchiveTest.xlsx is now available for editing. Chose Read-Write to open it for editing.' with a Read-Write and a Cancel button.