Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Summary

  1. Saving an Excel workbook sometimes runs very slowly;
  2. A huge temp file is created;
  3. You get a 'Document not saved' error;
  4. Your current Excel session cannot save the file, no matter what you do;
  5. You've lost all your work since the last successful save.

This is a request for information from anyone else who's seen this issue, inviting your suggestions for workarounds, recovery strategies, and preventative measures.

Details

Have you ever watched the folder your Excel workbook file is saved in, when you click 'Save' in Excel?

During the save process, Excel creates a temp file, with an arbitrary 8-character name and no '.xls' or '.tmp' extension. As soon as the write operation to this temp file is completed, the original file is deleted and this temp file is renamed to the original file name.

Sometimes this process goes wrong. Specifically: the Excel Application window stops responding to user input, but the screen still repaints and a 'Saving [filename] :' progress bar is visible in the application window.

The temporary file grows, and grows... And Grows.

Shortly after 2 GBytes - or if you run out of disk space - the temp file is deleted and Excel returns the 'Document not saved' error. This will take 10-15 minutes if you're lucky, and several hours if you're not. If you don't have an earlier copy of the file with your changes (or something in the autosave folder), you're completely out of luck: all your work since the last successful save is lost.

Nothing works: saving again repeats the process, 'Save As' into another file format (eg: a web page) repeats the process, after a delay.

I've seen this problem in four different companies; one of them (a large financial institution in New York, London, Zurich and Singapore) uses very large and complex spreadsheets with a lot of VBA, and most of the Excel developers have seen the problem.

Microsoft have nothing to say about it. A close reading of Microsoft's KB article on 'Document not saved' suggests that someone in Redmond has seen each separate part of the problem, but can't quite bring the parts together in a KB article that would acknowledge this specific issue.

Here's everything I know about it:

  1. I've only ever seen it over a network drive;
  2. I've only ever seen it after I've worked on VBA code in the file AND hit 'compile';
  3. I've mostly seen it with large files (over 50 MBytes) but today I saw it with a 780-kB file;
  4. I can't save the file from the current Excel session;
  5. I can export individual VBA modules;
  6. The network administrator is very annoyed about the traffic and the server space.

Here's some things I don't know:

  1. I haven't seen the problem in Excel 2007 or 2010, but I haven't used these later versions of Office enough to conclude that they don't exhibit this issue.
  2. All other versions of Excel from 1997 onwards can and will.
  3. I am not aware of any correlation with service packs, hotfixes, or third-party antivirus software.

Questions

  1. What is the cause of this problem?
  2. Can you recover from this error?
  3. What can you do to prevent it?
share|improve this question

migrated from stackoverflow.com May 23 '11 at 21:30

This question came from our site for professional and enthusiast programmers.

2  
Each application has 2gb of address space in windows. Is it a coincidence you get a fail at 2gb with a 2gb temp file? –  Nat Dec 2 '10 at 20:15
    
It's no coincidence! The buffer file is almost certainly written from a byte array which, by good luck or good programming, approaches a trigger level close to 2GBytes without aborting, so that some kind of error-handler halts the process. However, I don't think that this is line of enquiry leads to a usable workaround: Excel's internal memory model differs in unexpected (and undocumented!) ways from the programming you and I were taught, and the memory usage stats you see in Taskman (and most debugging tools) are rather misleading - as if we could even try to debug and fix Excel. –  Nile Sep 6 '11 at 14:57

4 Answers 4

Microsoft published this KB article in 2005, and it has recently become visible to search engines: may receive an error message when you try to save files in Excel 2000. This article describes using the NetworkResiliency registry setting. I would be interested to know whether anyone else has tried this.

The same information was published in KB291204 in 2007: You receive an error message when you save a file after the network connection is lost in Excel.


What I have done is to rename the massive file (I use an indicator like appending "junk" or "massive" to the file name) and then open the massive file and create a new ExCel workbook. I created a blank page with the same name for each page in the massive file. Then I copied only the part of the file that is useful avoiding copying entire rows of columns. Then I saved the new workbook as the original name. Then I closed the massive polluted file and saved it in a "just in case" folder. (I don't delete it in case there were some copying errors. I find that Excel occasionally has dangerous copy and paste errors.)

This is a bit tedious, but it has reduced files of around 160MB to around 8.25 MB.

I have done some research on the web regarding this problem to no avail. I hope this is helpful.

share|improve this answer
    
This does not address the question: you are describing the basic housekeeping task of reformatting and saving a bloated workbook. The original question is about a problem in the 'save' process which can occur with workbooks files of any size; the 2GB file described in the question is a temporary buffer file created by the Excel application during the save process. Feel free to offer further advice when you have studied the original question in more detail. –  Nile Sep 6 '11 at 14:40
    
Did you mean to comment and give advice for your own post? –  Dave Oct 26 '12 at 14:24

I'm unsure whether Excel is saving a temp file in my case. Otherwise my experience corresponds with points 1, 2, 3, 4 and 5 listed in the question. I'm working with Office 11.0 (2003).

To clarify, the second points 1 through 5 AND the first. I don't have an angry admin (yet!).

One workaround is to modify your file to be read-only, then whenever prompted to save you'll have to save with a different file-name and later overwrite the old file (and make the new one read-only once again). It'll save you having to export all of your modules.

A hassle, but less so I think than exporting each module and reimporting them.

I'm beginning to think if you use a reasonably complex piece of software long enough you'll eventually find a very poorly (or not at all) documented bug for which there isn't a fix.

share|improve this answer

Here is my workaround for this very frustrating problem. The only way to not lose all data is to copy all the info from the workbook while it is still open and paste it into a new workbook, which you can then save.

Presumably creating a new document enables Excel to launch a new temp file and path.

Interestingly, it seems that another workbook that was open, and had had the same "document not saved" message, saved and closed OK, after I started a new workbook for the other file, as I have described. However, I can't be 100% certain about this - I will try it again the next time I get this annoying problem.

I can see various temp files on the network drive but I didn't get into them. Life is too short.

share|improve this answer

Your work may not be lost, so do not panic and close the file. Here's something you can try: I have (sometimes) been able to copy the worksheets from the file which was not saved into a new or an existing worksheet. That file, with the copies of my work, can then be saved. Of course, once Excel crashed completely--but it repaired the file with the copies; while the original file reverted to a very old version, my work was not lost. Good luck.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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