UPDATE 2 (3/21/17)
I have discovered that trying to open an Excel book after hitting the Submit button once happens only when one (or all) of the imported sheets from the module are deleted. (The process removes the old sheets before re-submitting to clear out the workbook to start over). For a manual test, I hit the submit button, delete any of the imported worksheets then try to open any excel file and it crashes. I have also ensured all defunct named ranges are removed when the sheet is deleted. I also tested this on a file that just imports a blank sheet. Then I delete it and am able to open workbook just fine. I'd like to avoid having to create my module (since it's a kind of a drag).
Original Question
I have an Excel Workbook Tool that opens other Excel Workbooks and imports worksheets from those workbook after processing some information.
In total there are 5 module workbooks. At a high level these workbooks are the same - sheet structure, general code structure, etc. There are differing formulas and some named ranges are different, etc.
In the main tool, there is the ability to re-run the code that pulls the information from the different workbooks. It essentially resets the original workbook and then runs the code again. This is done without closing the original workbook. (A user can refresh web-service data and re-run the tool).
The problem I am facing is that when I re-run the process for two of the modules workbooks Excel crashes during the re-run at that point where the code attempts to open the module workbook. The other 3 module workbooks work great. I can run and re-run and re-run ... The other 2 crash every time.
I have done a ton a research on the files to see why this could happen, but have not found out why. There are no links left in the main workbook after the process runs, no data connections, no bad links etc.
Also, the interesting thing is that the files I store in the UAT environment folder work fine all the time. The files in the production folder fail. I even copied the files directly from the UAT environment folder to the production environment folder and it still fails. I have also ruled out permission and security at the folder level.
I can also open the file manually after submitting the code the first time.
I realize this may be slightly out of scope for SO and a little vague but was hoping someone may have had a similar experience and could shed some light.
Update
The relevant code is below. Based on the comments by @Ralph I forced a memory wipe by adding the line Set wbLOB = Nothing
, but unfortunately, issue still happens.
Function LoadLOB(sLOB As String, sXMLFile As String) As Boolean
Dim sLOBFile As String
sLOBFile = wsReference.Range("ModuleFolder").Value2 & sLOB & "\" & sLOB & ".xlsb"
Dim wbLOB As Workbook
Set wbLOB = Workbooks.Open(sLOBFile) '--> 2nd run crashes on this line.
If TieXMLToExcel(wbLOB, sXMLFile, sLOB) Then
MapXMLFieldsToExcelCells wbLOB, sLOB
Select Case sLOB
Case Is = "Property"
SortTableByAscendingColumn wbLOB, "xml" & sLOB, "tCommonLocationProperty", "Location_ID"
SortTableByAscendingColumn wbLOB, "xml" & sLOB, "tLocationByCoverageTypeProperty", "Location_ID"
Case Is = "GeneralLiability": SortTableByAscendingColumn wbLOB, "xml" & sLOB, "tClassCodesByLocationGeneralLiability", "Location_ID"
Case Is = "CommercialAuto": SortTableByAscendingColumn wbLOB, "xml" & sLOB, "tVehicleSummaryCommercialAuto", "AuVehicleNo"
Case Is = "Crime": SortTableByAscendingColumn wbLOB, "xml" & sLOB, "tCommonLocationCrime", "Location_ID"
End Select
Application.Run wbLOB.Name & "!PrepareSheetForMasterFile", ThisWorkbook
wbLOB.Close False
LoadLOB = True
End If
Set wbLOB = Nothing
End Function