I have an inherited spreadsheet that automatically runs some VBA code and then exits. This was last run in early October and ran fine. Now, it fails with this error:
Microsoft Excel has stopped working
Windows can try to recover your information and restart the program.
-> Restart the program
-> Debug the program
If I try restarting, it gives me
Automation error Exception occurred.
(Actually, it usually only gives me the option to close or debug the program, but I got the restart option once, on one machine, so that was another point of information.)
It is loading a set of file names into an array, and then reading through them, in order to load data to a database. It is failing on files that have been unchanged, are from 2015. The Excel itself is also unchanged, but that doesn't mean there hasn't been some upgrade to Office that is making it act differently. I'm leaning towards the error handling not skipping an error the way it used to.
The following code fails, as soon as it gets to an array entry that doesn't have a value:
FName = Array("april2010.xls", "feb2010.xls", "jan2010.xls", "july2010.xls", "june2010.xls", _
"mar2010.xls", "may2010.xls", "sep2010.xls", "..\FINAL-MO-BAL-2011\APRIL2011.xls", _
"..\FINAL-MO-BAL-2011\AUG2011.xls", "..\FINAL-MO-BAL-2011\DEC2011.xls", _
"..\FINAL-MO-BAL-2011\FEB2011.xls", "..\FINAL-MO-BAL-2011\JAN2011.xls", _
"..\FINAL-MO-BAL-2011\JULY2011.xls", "..\FINAL-MO-BAL-2011\JUNE2011.xls", _
"..\FINAL-MO-BAL-2011\MARCH2011.xls", "..\FINAL-MO-BAL-2011\MAY2011.xls", _
"..\FINAL-MO-BAL-2011\NOV2011.xls", "..\FINAL-MO-BAL-2011\OCT2011.xls", _
"..\FINAL-MO-BAL-2011\SEP2011.xls", FName2, FName3, FName4, FName5, FName6, _
FName7, FName8, FName9, FName10, FName11, FName12, FName13, FName14, FName15, _
FName16, FName17, FName18, FName19, FName20, FName21, FName22, FName23, FName24, _
FName25, FName26, FName27, FName28, FName29, FName30, FName31, FName32, FName33, _
FName34, FName35, FName36, FName37, FName38, , FName39, FName40, FName41, FName42, _
FName43, FName44, FName45, FName46, FName47, FName48, FName49)
If IsArray(FName) Then
Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
WorkbookName = ThisWorkbook.Name
rnum = 1
For Fnum = LBound(FName) To UBound(FName)
Set mybook = Nothing
On Error Resume Next
Set mybook = Workbooks.Open(Filename:=FName(Fnum), ReadOnly:=True)
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
.CutCopyMode = False
.DisplayAlerts = False
.Visible = False
End With
On Error GoTo 0
If Not mybook Is Nothing Then
On Error Resume Next
(and the code continues to read through and parse each file.)
If, however, I comment out the last line of the array setup, so it stops at FName42, which is the last entry with a value, it will work (and then fail in later code as soon as an array entry is empty (because the Dec 2017 file isn't there yet).)
I'm not a VBA programmer, and I've tracked down the error to this section by putting in MsgBox lines and running it until it dies. It's hitting something that is completely killing Excel. It does this on my Win10 machine and my Win7 machine (and a co-worker's machine).
We have Excel 2016 and I know we also have Office 365 on the computers now.
Any ideas of what to try so it will continue processing?
