Two large, linked Excel files take 30 minutes to save, except in VMWare environment

Question

I support some tax consultants who love to use Excel when they should probably be using Access. Anyway, they have created two Excel files, A and B. File B has cells linked to file A. File A is 27 MB and file B is 16 MB. One worksheet has roughly 1 million rows and there is another worksheet doing a whole bunch of SUMIF on the 1 million rows. Not the best idea, but whatever.

Both Excel files open and recalculate within a reasonable amount of time (1-2 minutes). For a files that large, this is acceptable.

Here is the problem: Once you change a cell, and save the file B, it takes a solid 30 minutes to save the file, and the processors are going full speed. I've tried this on 6 different machines, all running Windows XP SP3 with Office 2007 SP2 and all patches. The specs vary from one machine with 512 MB or RAM to a machine with 4 GB of RAM and quad processors. Same result every time.

Here is the clincher: If I do this same save operation on a VMWare virtual machine, the file gets saved in 1 minute. I've tried this with my ESX servers at the office, my Mac Fusion at home, and VMWare workstation at the office. It does not matter how much RAM the virtual machine has... it saves in about 1 minute every time.

Does anybody have any idea why this is happening and how to fix?

Answer 1

I suggest unchecking the "Save External Link Values" option under: Excel Options -> Advanced -> When Calculating This Workbook:

I think that this should solve your problem. Also doing some research on this topic pointed out to me that this problem of large linked files has been in Excel for a while. The following is a description of almost the exact problem you are having from 2003. Don't ask me why the VM is not having the same problem - although I work with large Excel files a lot to me Excel development is still somewhat of a dark art... You are never too sure about what's going on in the background.

( http://www.mrexcel.com/forum/showthread.php?t=43769 )

I have just started doing more advanced work with Excel and have run into a problem. I have a workbook (workbook A) with several worksheets. One of the worksheets contains several columns with "SUMIF" functions pulling information off of another large workbook (workbook B) with around 7000 rows. When I have Workbook A and B open at the same time and try to save either one I get the "Not Enough Memory" and "Cannot Save External Links" error messages. The solution I found on the Microsoft Support website was to uncheck the "Save External Link Values" box on the calculation tab of the options dialogue box. I did this and I no longer get the error messages - it also doesn't seem to affect the useability. I can e-mail the workbook to others and the values are ok. I can update the sheet and everything seems fine. But, I am worried that somewhere down the line I will have problems. What exactly does unchecking this box do? How does it affect the way the workbook works? Thank you in advance for your help.

Looks like Excel is still dealing with the same problems it was dealing with almost 10 years ago...