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
'Open the source file and assign values to xlApp, xlWorkBook, and xlWorkSheet.
If OpenImportFile() Then
'Verify the format of the source file.
If (VerifyCaseRows() And VerifyColumnNames()) Then
'The data file has been found and opened. Import and stage the CASE information.
If ImportHeader(CaseData) Then
If ImportClaimData() Then
MsgBox "Import Complete."
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", _
Me.RecordSource = "tblArchiveCaseStaging"
If Me.txtFileNumber.Visible Then Me.txtFileNumber.SetFocus
On Error Resume Next
If Not xlWorkSheet Is Nothing Then Set xlWorkSheet = Nothing
If Not xlWorkBook Is Nothing Then
Set xlWorkBook = Nothing
If Not xlApp Is Nothing Then
Set xlApp = Nothing
Trap.Handle Me.Name, "cmdImportCase_Click"
The Excel variable declarations are module-level:
'Early bound - FOR DEVELOPMENT ONLY
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
'Late bound - FOR RELEASE VERSIONS
'Dim xlApp As Object
'Dim xlWorkBook As Object
'Dim xlWorkSheet As Object
The objects themselves are assigned in OpenImportFile. There is some extra stuff, but the actual assignments are pretty standard:
'Open a new session of Excel and then open the workbook.
Set xlApp = CreateObject("Excel.Application")
'Open the indicated workbook and worksheet
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.