Excel crashes when opening a workbook using VBA

 2021-06-17    Problem solving    0    11

I have experienced lots of Excel crashes when trying to open a workbook from VBA.
This has occurred both when opening workbooks from a "normal folder" (e.g. C:\Folder\filename.xlsx) or when opening workbooks from a OneDrive folder (where the remote files are synced to a local folder).

I have no explanation to why this happens, but I suspect the problem is caused by slow network speed or VPN connection.
I found that the fix for this problem seems to be to add a short pause in the code after opening a workbook, so far this seems to prevent Excel from crashing when opening a workbook using VBA.

Sub ThisMightCauseExcelCrash()
    Dim varFiles As Variant
    varFiles = Application.GetOpenFilename("Excel Workbooks (*.xl*),*.xl*", 1, "Select one or more workbook(s):", , True)
    If VarType(varFiles) = vbBoolean Then Exit Sub ' user cancelled
    
    Application.ScreenUpdating = False
    Application.Cursor = xlWait
    Dim i As Long, wb As Workbook, OK As Boolean, lngCount As Long
    For i = LBound(varFiles) To UBound(varFiles)
        OK = True
        On Error GoTo ErrorHandler
        Set wb = Workbooks.Open(CStr(varFiles(i)), False) ' this line might cause Excel to crash
        On Error GoTo 0
        If OK Then
            lngCount = lngCount + 1
            ' do something with the workbook
            'wb.Close True ' save and close the workbook
            wb.Close False ' close the workbook without saving any changes
        End If
    Next i
    Application.Cursor = xlDefault
    Application.ScreenUpdating = True
    MsgBox "Count of processed workbooks: " & lngCount, vbInformation, ThisWorkbook.Name
    Exit Sub
    
ErrorHandler:
    OK = False
    Resume Next
End Sub
 

' add this to the top of the code module, after the Option Explicit statement
#If VBA7 And Win64 Then ' 64 bit Office and 64 bit Windows
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) ' pause code execution
#Else ' 32 bit Office, 32 or 64 bit Windows
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) ' pause code execution
#End If

Sub ThisMightPreventExcelCrash()
    Dim varFiles As Variant
    varFiles = Application.GetOpenFilename("Excel Workbooks (*.xl*),*.xl*", 1, "Select one or more workbook(s):", , True)
    If VarType(varFiles) = vbBoolean Then Exit Sub ' user cancelled
    
    Application.ScreenUpdating = False
    Application.Cursor = xlWait
    Dim i As Long, wb As Workbook, OK As Boolean, lngCount As Long
    For i = LBound(varFiles) To UBound(varFiles)
        OK = True
        On Error GoTo ErrorHandler
        Set wb = Workbooks.Open(CStr(varFiles(i)), False) ' this line might cause Excel to crash
        On Error GoTo 0
        Sleep 500 ' wait 500 milliseconds after opening a workbook to prevent Excel from crashing (you might have to adjust this delay)
        If OK Then
            lngCount = lngCount + 1
            ' do something with the workbook
            'wb.Close True ' save and close the workbook
            wb.Close False ' close the workbook without saving any changes
        End If
    Next i
    Application.Cursor = xlDefault
    Application.ScreenUpdating = True
    MsgBox "Count of processed workbooks: " & lngCount, vbInformation, ThisWorkbook.Name
    Exit Sub
    
ErrorHandler:
    OK = False
    Resume Next
End Sub