Posts by JayMoore74

Thanks Ger. Yes, I have rebuilt hundreds of workbooks from scratch exactly as you've described. In this situation it's ~8 hours, because the named ranges and time it takes to rebuild the calc trees. However, it 100% doesn't fix the problem, the files keep getting corrupted upon saving (they never crash while I'm actually using them). I'm still willing to pay someone thousands of dollars to find the real problem.

I have dozens of very large MySQL databases on AWS - I'm not using spreadsheets to warehouse data (and Ctrl + End takes me to the end of my formulas). My spreadsheets are as efficient as can be while still performing their function (financial analysis & backtesting).

Lastly OneDrive has been pretty solid - syncing ~600GBs across 4 PCs throughout the country. It has been much better than Google Drive for me.

I did find a solution. Take the following steps;

1. Before you save the file, open the Visual Basic editor and on the top menu click Debug -> Compile VBA Project

2. Save-As when you save the file (new file name so it’s a fresh file)

3. Close Excel completely

4. Open Excel in safe mode by holding the Control key when you open Excel

5. Since that opens a blank workbook, in safe mode Excel go to File -> Open and open your new spreadsheet you just saved

6. Open VB editor and click Debug -> Compile again. Maximize the VB window if its not already. Then close it.

7. Save-As to another new file name.

8. Close safe mode excel and open the file from #7 above normally...hold your breath

This works 90% of the time on my 300MB to 1.8GB Excel files (xlsm and xlsb).

Cheers,
Jason

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?

My Machines

• 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.

My Spreadsheets

• 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?

Re: create multiple copies of the same page and export to a single PDF

This is exactly what I need to do. Did you find a better way? I saw someone supposedly use a program called pdfforge to do something like this, but I would like to avoid that if possible.

To summarize, I need to print the same Excel tab 20x, with different data each time, to a single PDF document. It shouldn't be that hard.

I have a workbook that crashes when running certain macros (Error message "Automation Error Exception Occurred" and then Excel closes). I'm confused because it only crashes when I have other programs running while attempting to run the macro. Right now, I have 2 Internet Explorer windows, Outlook, and one other program open. If I try to run the macro Excel crashes. If I close all those other programs, the macro runs fine.

The macro involves opening a file on the network, copying all cells, pasting them to a specific worksheet within the workbook, then closing the workbook I copied from. It does this about ten times. It always crashes while trying to close the 2nd workbook (Workbooks("Allport.xls").Close).

Sub UpdateSMAModel()
'
'
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    RB = ActiveWorkbook.Name
    
    ChDir "P:\Portfolios\SMA"
    Workbooks.Open Filename:="P:\Portfolios\SMA\SMAModel.xls"
    Cells.Select
    Selection.Copy
    Windows(RB).Activate
    Sheets("SMA Model Hard Coded").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Workbooks("SMAModel.xls").Close
    Windows(RB).Activate
    
    ChDir "P:\Portfolios"
    Workbooks.Open Filename:="P:\Portfolios\Allport.xls"
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(RB).Activate
    Sheets("Allport").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Workbooks("Allport.xls").Close
    
    Windows(RB).Activate
    ChDir "P:\Portfolios\SMA"
    Workbooks.Open Filename:="P:\Portfolios\SMA\AllSMA.xls"
    Application.CutCopyMode = False
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows(RB).Activate
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1").Select
    Windows("AllSMA.xls").Activate
    ActiveWindow.Close
    
    Windows(RB).Activate
'code continues but I cut it short

When I enabled alerts, I was able to find this error code as the macro crashed:
-2147417848 (80010108) The object invoked has disconnected from its clients.

I believe my problem is that I am using "Unqualified Code" (see website below)
http://support.microsoft.com/d…spx?scid=kb;en-us;Q319832

However, I am not sure how to "Qualify" my code. I'm not that advanced and the website above doesn't put it in simple enough terms for me to understand. I have 3 spreadsheets that all have this same problem (they crash sometimes, and run fine other times). I'm using a brand new dual core Dell computer, Windows XP, Excel 2003.

Thanking you in advance for your help or suggestions