2

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
Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
  • Welcome to SO. Please read [How to Ask](http://stackoverflow.com/help/how-to-ask) .In short, you'll need to show your code attempts to solve this and where it's not working to get the most help on this site. If you also post a screenshot of a sample of your sheet, that would help us help you more as well. – Moosli Mar 14 '17 at 16:02
  • 4
    @Moosli - *Welcome to SO?*- Please visit my profile or simply look at my rep points. And read my question. I am *very aware* that I am posting a question a bit out of scope. – Scott Holtzman Mar 14 '17 at 16:05
  • If I have understand, you copy the workbooks in another folder, that you named it "production folder". May be the name of this folder is tall, so Excel cannot open a file in it? – D. O. Mar 14 '17 at 16:09
  • @D.O. - Thank you. However, please re-read question. Process runs perfectly *the first time* for all modules. When running a second time, it crashes Excel when trying to open 2 of the 5 modules. – Scott Holtzman Mar 14 '17 at 16:10
  • @Moosli --> Seriously!! – D. O. Mar 14 '17 at 16:11
  • 1
    `Crashing on the second run` sounds to me like (1) you're importing something which exists already there. For example a sheet with the name "sheet1" is already imported and when you try to import another sheet with that name then you get an error. The same can happen for named ranges, query links, references, and a lot of other *hidden* content in Excel files. AND / OR (2) Not all variables are reset before they are used again. VBA is very forgiving in that respect and normally does not require this. Yet, when you are running complex VBA macros it normally results in errors / crashes. – Ralph Mar 14 '17 at 16:13
  • @Ralph - Thanks for the comment. And I agree with everything you say and have checked for all those things in point 1. Also, the code crashes **directly** on the `Workbooks.Open` method. So no processing even happens. Point 2 is interesting and gives me a new place to look - which I sorely need in this situation). But the thing about that idea is that 3 of the modules work great, but 2 do not. – Scott Holtzman Mar 14 '17 at 16:22
  • Can you share the project with us? Perhaps you should check the install of Office if you are having inconsistent results on different computers. E.g. works on one computer, but not another. – Ryan Wildry Mar 14 '17 at 16:43
  • @RyanWildry - I can't share project. Even if I could it would be wildly challenging to replicate. It's tied to a few internal web services and SQLServer databases. Fortunately, I don't have the works on computer but not another issue. – Scott Holtzman Mar 14 '17 at 16:46
  • @Ralph - Unforutately, destroying the object explicitly didn't help. I know there's like on small thing in the two modules that is causing the issue. But its very hard to find, especially given the process works in one folder (UAT) and not the other (prod). – Scott Holtzman Mar 14 '17 at 17:09
  • 1
    I am just wildly guessing from my own experience in larger corporate environments with several servers across the globe and citrix-farms. So, here is another shot: Are both folders on the same server? I once had one folder on a windows-based server using the `\ ` for folders and sub-folders and the other files were hosted on a SharePoint server where folders and sub-folders needed to be addressed using `/` so `\\win1\test` would become `//sharePoint1/test`. Also, did you use `.Open` with the options `ReadOnly` and `UpdateLinks = False` (at least for testing / debugging)? – Ralph Mar 14 '17 at 17:16
  • Try running excel in safe mode for the second pass when it normally crashes. Also try toggling some of the parameters for workbook.open, like disabling updateLinks, ReadOnly etc. Also, did you intend to pass the strings byRef? Where is wsReference defined? Perhaps that should be cleared too? – Ryan Wildry Mar 14 '17 at 17:19
  • @Ralph - Yes. UAT and Production are on same server. I just tried `ReadOnly` and `UpdateLinks:=False`. No dice. Thanks for all your efforts. – Scott Holtzman Mar 14 '17 at 19:24
  • @RyanWildry - Workbooks.Open option toggling unfortunately didn't help, but I appreciate the different idea. I didn't explicitly intend to pass ByRef or ByVal, but that is not an issue. wsReference is a worksheet code in the main file. Thanks for looking at this. I do appreciate the group-think - hence why I posted a question a bit off the beaten path. – Scott Holtzman Mar 14 '17 at 19:30
  • One last thing: check for the file existence / accessibility (accessible to Excel) before opening the file using [If Dir(sLOBFile) <> "" Then](http://stackoverflow.com/questions/11573914/check-if-the-file-exists-using-vba). I am thinking here of some kind of file-server-lock on the file if the file is opened / closed too fast. – Ralph Mar 14 '17 at 19:32
  • No problem Scott. Hopefully our spitballin' is helping some. Sounds like a weird issue. – Ryan Wildry Mar 14 '17 at 19:32
  • @ScottHoltzman i had once a similar Error. Somehow, don't ask me why did excel loos the Application Object. so (1) i would try "Application.Workbooks.Open(sLOBFile)" (2) what happen if you set a breakpoint by the second run to "Workbooks.Open(sLOBFile)" and when it stops there, you open the File manually on the Server? – Moosli Mar 15 '17 at 12:36
  • @Moosli - Sure I can take a joke. However, I did not find any indication that there was a joke in your comment. Thanks for the suggestion about `Application`. I did try your 2nd point. And it also crashes. :( – Scott Holtzman Mar 15 '17 at 13:28
  • @ScottHoltzman ok sorry for that. (1) Thanks for that Information. Does the file crasehes aswell if you let your code run once and after that, you just open the file on the server? (without running the code a second time) (2) do you save the file after you extract the data? – Moosli Mar 15 '17 at 14:05
  • @ScottHoltzman Did you try my last suggestion with `Dir()`? Anyhow, it would be nice to keep us posted in case you found a solution or were able to circumvent the problem. – Ralph Mar 16 '17 at 14:11
  • @Ralph - Yes, I did it. It showed file as available. I will update you guys as soon as I find the solution. Things are moving fast here with many parts to this project. Will surely update as I solve the issue. Thanks for the follow-up. – Scott Holtzman Mar 16 '17 at 14:59
  • 1
    @Ralph - See update at top of question. – Scott Holtzman Mar 21 '17 at 15:00
  • @RyanWildry - See update at top of question. – Scott Holtzman Mar 21 '17 at 15:00
  • If `TieXMLToExcel` returns `False` then `wbLOB` gets opened but not closed. You may need to move the `wbLOB.Close False` statement outside the `If` block. – xidgel Mar 21 '17 at 17:14
  • Thanks @xidgel. Right now the functions never fail. (I realize I didn't specify that in my question). – Scott Holtzman Mar 21 '17 at 18:50
  • 1
    @Ralph - ended up re-creating file from scratch and it works now. Most likely small corruption that could not be detected. – Scott Holtzman Mar 24 '17 at 19:35

2 Answers2

2

I doubt this is the answer, but I figured this is a better forum for exchanging the ideas I had on this issue. What I did is I grabbed some windows APIs to check to see if that file is open before trying to open it again. I also added a method to close the file, and I made the SaveChanges parameter more explicit. I also added a few DoEvents in there, in case something is waiting to finish.

Hopefully this is a launching pad for other ideas. I hope some of this helps.

'Determine whether a file is already open or not
#If VBA7 And Win64 Then
    Private Declare PtrSafe Function lOpen Lib "kernel32" Alias "_lopen" (ByVal lpPathName As String, ByVal iReadWrite As Long) As Long
    Private Declare PtrSafe Function lClose Lib "kernel32" Alias "_lclose" (ByVal hFile As Long) As Long
#Else
    Private Declare Function lOpen Lib "kernel32" Alias "_lopen" (ByVal lpPathName As String, ByVal iReadWrite As Long) As Long
    Private Declare Function lClose Lib "kernel32" Alias "_lclose" (ByVal hFile As Long) As Long
#End If

Function LoadLOB(ByVal sLOB As String, _
                 ByVal sXMLFile As String) As Boolean

    Dim sLOBFile As String
    Dim wbLOB    As Workbook
    sLOBFile = wsReference.Range("ModuleFolder").Value2 & sLOB & "\" & sLOB & ".xlsb"

    'Make sure the file is closed before processing
    If Not isFileOpen(sLOBFile) Then
        Set wbLOB = Workbooks.Open(sLOBFile, 0, False)
    Else
        'Close it if it is open
        closeWB sLOBFile
        Set wbLOB = Workbooks.Open(sLOBFile, 0, False)
    End If

    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
        DoEvents
        wbLOB.Close SaveChanges:=False
        LoadLOB = True
    End If

    Set wbLOB = Nothing
End Function

Sub closeWB(ByVal FilePath As String)
    Dim wb  As Workbook

    For Each wb In Application.Workbooks
        If wb.FullName = FilePath Then
            wb.Close SaveChanges:=False
            Set wb = Nothing
            DoEvents
            Exit For
        End If
    Next

End Sub

Function isFileOpen(ByVal FileName As String) As Boolean
    Dim FileNumb As Long: FileNumb = -1
    Dim lastErr  As Long

    FileNumb = lOpen(FileName, &H10)

    'Determine if we can open the file
    If FileNumb = -1 Then
        lastErr = Err.LastDllError
    Else
        lClose (FileNumb)
    End If

    ' Check if there is a sharing violation and report back status
    isFileOpen = (FileNumb = -1) And (lastErr = 32)
End Function
Ryan Wildry
  • 5,612
  • 1
  • 15
  • 35
  • thanks Ryan. I'll definitely take look and play with these ideas. Everyone where I work agrees it's **very** bizarre. – Scott Holtzman Mar 15 '17 at 16:32
  • I was really hoping this would help, but `isFileOpen` returns false after running the first time :(. Either way, once I solve it I will update question with the answer. Also, I placed this code into my file directly after trying pieces of it and still no dice. – Scott Holtzman Mar 16 '17 at 21:06
  • 1
    This might be helpful: https://support.microsoft.com/en-ca/help/2939112/how-to-resolve-crash-and-stability-issues-in-office-programs-by-using-the-offcat-tool. – Ryan Wildry Mar 16 '17 at 22:55
  • ended up re-creating file from scratch and it works now. Most likely small corruption that could not be detected. – Scott Holtzman Mar 24 '17 at 19:36
1

Not sure how helpful this answer will be, but I re-created the module files from scratch and they worked in all environments. Most likely a small corruption in the file that I could not find.

Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72