Excel 2010 64-bit Randomly Corrupting Files

[goss8572]

I run a set of large, inter-linked spreadsheets that are used for a Financial Model. We were pushing the memory limits of 32-bit Excel, so we switched to 64-bit (2010). I have recently been running into an issue where random files seem to get corrupted during the save process. The files appear to save correctly, but the next time I try open them, one of them hangs and never opens. There appears to be no rhyme or reason as to which file experiences the issue...it varies. Furthermore, the error does not occur every time. Sometimes all files save properly. It seems that around 50% of the time, one of the files gets corrupted. I have found that 32-bit Excel will still open the corrupted file (even though 64-bit will not), and simply saving the new file once it has been opened in 32-bit corrects the issue. I am then able to go back to 64-bit and open the file and it works properly. Any info as to why the files are randomly getting corrupted would be appreciated. Thank you.

 

[goss8572]

I posted this a few months back and never got any replies. Hoping someone can help!

Thank you!

 

[RobExcel]

We've been having the same problem for at least a year (Excel 2010 and 2013). The issue appears to impact any large Excel file. We were not pushing the memory limits of 32-bit Excel, but the files are large with many sheets. We generally have links to external files, though not too many files, and they are not inter-related. There are some macros and UDFs, though they are properly declared with ptrsafe and 64 bit compatible pointers.

When Excel 2007 was released we found that linking to Excel files created with a version of Excel prior to 2007, even if they had been fully converted to the 2007+ format, tended to crash Excel on open. We resolved that issue by religiously never directly linking to any file that had been created with an older version of Excel (and "re-built" some files in Excel 2007). I wonder if 64-bit Excel now has a similar problem with 32-bit created linked files...

For completeness, one such file that has consistent issues utilizes the following VBAProject References: "Visual Basic for Applications", "Microsoft Excel 14.0 Object Library", "OLE Automation", "Microsoft Office 14.0 Object Library", and "Microsoft Forms 2.0 Object Library".

Rob

I run a set of large, inter-linked spreadsheets that are used for a Financial Model. We were pushing the memory limits of 32-bit Excel, so we switched to 64-bit (2010). I have recently been running into an issue where random files seem to get corrupted during the save process. The files appear to save correctly, but the next time I try open them, one of them hangs and never opens. There appears to be no rhyme or reason as to which file experiences the issue...it varies. Furthermore, the error does not occur every time. Sometimes all files save properly. It seems that around 50% of the time, one of the files gets corrupted. I have found that 32-bit Excel will still open the corrupted file (even though 64-bit will not), and simply saving the new file once it has been opened in 32-bit corrects the issue. I am then able to go back to 64-bit and open the file and it works properly. Any info as to why the files are randomly getting corrupted would be appreciated. Thank you.

 

[RobExcel]

Microsoft has acknowledged this issue with Excel. Workbooks with any VBA at all are subject to excessive crashing in 64 bit versions of Excel. This affects all recent version of Excel (probably 2010 through 2016). Without going into details, the cause is that Excel does not correctly save the compile state of the VBA code and 64 bit Excel cannot recover from that issue when opening the afflicted Excel file (32 bit usually can). However, a fix was released for only Excel 2016. To make use of this fix you must change a registry entry. Here are the steps to make the registry change:

1. Exit all Microsoft Office applications.
2. Start Registry Editor:

• In Windows 10, go to Start, enter regedit in the Search Windows box, and then select regedit.exe in the search results.
• In Windows 7, click Start, type regedit in the Search programs and files text box, and then click regedit.exe in the search results.
• In Windows 8 and Windows 8.1, move your mouse to the upper-right corner, click Search, type regedit in the search text box, and then click regedit.exe in the search results.

3. Locate and then select the following registry subkey:
HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Options\
4. On the Edit menu, point to New, and then click DWORD Value.
5. Type ForceVBALoadFromSource, and then press Enter.
6. In the Details pane, right-click ForceVBALoadFromSource, and then click Modify.
7. In the Value data box, type 1, and then click OK.
8. Exit Registry Editor.

Best of luck.
Rob