Excel – Microsoft Excel returns the error ‘Document not saved’ after generating a 2GB temporary file

microsoft excel

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?

Best Answer

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.

Related Question