6

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?

  • are the VMware clients running XP SP3 with Office 2007 like the office machines? – Chris_K Apr 8 '10 at 17:11
  • Yes. In fact all of the machines tested, including the VMs, are based off the same corporate image. – Gerald L Apr 8 '10 at 17:15
  • Were the VMs 'fresher' (f.e. without updates) then the real PCs? – Bobby Apr 8 '10 at 17:26
  • No. Patch levels are all the same. – Gerald L Apr 8 '10 at 17:32
  • Are the VMs all on solid state drives? ;-) (yeah, that's a reach... ) – Chris_K Apr 8 '10 at 17:34
2

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

  • 1
    Thanks for this, Doruk. I made the settings changes referenced in this article and this did not fix the problem. – Gerald L Apr 15 '10 at 17:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.