First off, I want to thank everyone who makes this the best Excel help forum on the web. I have learned so much from this forum, and recommend it to everyone interested in Excel.
I have several large spreadsheets (900MB in XLSM format, 750MB XLSB) that frequently get corrupted. Sometimes they crash while I'm in them, sometimes they just won't open after supposedly saving/closing just fine, and sometimes they crash while saving/closing. Yes, I know, I am a fool and I'm not utilizing Excel correctly. I need to clean up my spreadsheets, or use different software... etc. But what if I'm not a fool, my spreadsheets are extremely efficient, and I am utilizing Excel correctly?
- Intel Core i7-6950X with 128GB of Registered ECC RAM, 1TB Samsung 960 Pro SSD, Windows 7 Pro 64 bit with Microsoft Office 2010 Pro 64 bit
- I have never been close to running out of RAM, the max I've seen utilized on the Task Manager is 35%.
- I have two of these exact machines, which might be important. One is in New York and the other is in California. The problems started around the time I started using this dual PC setup.
- I use LogMeIn to open these large spreadsheets from both locations, with Google Drive or OneDrive to sync them. I though Google Drive was corrupting them, so I switched to OneDrive.
- I try to set the default printer to Adobe PDF on both PCs, since I know differing/missing default printers can cause errors. I believe it did hurt me for a while before I realized it.
- Non-Microsoft Excel add-ins (potentially the problem); Bloomberg Excel Tools, SNL Office Excel, Name Manager Utility, Stock Market Functions Add-in. However, I have tried disabling all add-ins (even COM add-ins) and it doesn't seem to help. Plus they always end up magically re-enabling themselves... Lastly, I need these add-ins.
- Not that it matters but if you're inclined to help I'll satisfy your curiosity: They are algorithms to pick stocks and backtest the hypothetical trades, I work on Wall Street.
- There are no external links, no visible or hidden named ranges with errors, no ActiveX, no custom formulas, no data connections, and no blank cells taking up space (i.e., the ctrl + end trick).
- There are no wasted formulas and as few array formulas as possible (sometimes they are required)
- In case it matters, there were data connections (ODBC, MySQL 5.3), but I thought they were the problem and recently replaced them with hard-coded tables (they are simply ranges, not formatted Excel "tables") pulled in via VBA
- There are a few userforms, and <10 VBA modules with <100 lines of code each. All modules are very small in size (few KBs at most)
- Each spreadsheet only has 15-20 sheets, nothing crazy
- One sheet in each model is huge (60,000 columns by 2,000 rows). Not a single error or wasted cell though.
- I run manual calculations, but it only takes ~15 seconds to calculate the whole thing
The Errors / Corruption Issues
- All over the map...
- To be safe, I always open a copy of the file NOT being synced by OneDrive (i.e., a copy on my Desktop and not in the OneDrive folder).
- Today I got the "Module not Found" error (when I press OK Excel crashes) upon opening an XLSM file that I successfully saved 5 minutes ago. The VBA project has 6 references, and they're all legit and available. The only one I added manually was ActiveX Data Object 6.1 which I need to upload to MySQL. I tried removing a few of them and it didn't fix the problem. So I revert back to the good Excel file from last Friday, but anytime I update and save it...it gets corrupted again.
- In the past the spreadsheets just wouldn't open, and the appcrash was due to EXCEL.EXE or VBE7.something but Googling the errors never led anywhere. Perhaps we could explore this more?
I have rebooted my PC, repaired MS Office, and installed all Windows and Office Updates.
So right now, I have a perfectly good spreadsheet that opens and runs macros with no problem. If I save it right now it will corrupt itself, it happened 4x today.
I will pay someone to help me figure this out. Any software or consultant suggestions?