2

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?

Community
  • 1
  • 1
thursdaysgeek
  • 7,696
  • 20
  • 78
  • 115
  • Installing newer versions of any microsoft products can change your registry and then some macros can crash your Excel. Also, the same goes for some add-ins. If you are running the macro on a server, pay attention to user's security setting, you need to have all read, write and edit rights. If you are connected to an Access database, you need to make sure you have database engine installed, Excel crashes when trying to establish a connection. There are many possibilities. It will be a challenge to find out what causes the problem. If you can get any error number, start from there – Ibo Dec 15 '17 at 21:47
  • Automation errors are most likely caused by updates (either by Microsoft or installing a Microsoft product), you need to clean your registry in that case. find the automation error number and search on Google, there are instructions how to fix it – Ibo Dec 15 '17 at 21:48
  • How do I find the automation error number? – thursdaysgeek Dec 15 '17 at 22:16
  • The number is displayed on the standard error message, if you don't get any error message, then you have to figure out different ways, one is to start the Excel application on a safe mode. Hold down CTRL key and start your Excel, that way it will ask you whether to start on the safe more or not – Ibo Dec 15 '17 at 22:35
  • Test that the file exists with `If len(Dir(thesentence)) Then` before opening the workbook and run all the parsing from a second hidden instance of Excel to avoid the crash of your main script: `Set xl = New Excel.Application`. – Florent B. Dec 16 '17 at 14:14
  • the question title says `I didn't change anything` ... think about it .... the date has changed ... roll your computer's calendar back a few days and try the code again (just a wild guess) – jsotola Dec 18 '17 at 04:06

0 Answers0