Microsoft Excel returns the error 'Document not saved' after generating a 2GB temporary file

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?

2

8 Answers

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.

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.

1

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.

1

I ran into this same error and copying/pasting into a new workbook wasn't really an option to me b/c I had some very detailed/customized pivot tables. I attempted to save it to a local drive (instead of the network drive I was using) as the Microsoft support article advises but even that didn't fix it.

I ended up just saving it as a Macro Enabled Workbook (.xlsm) instead of just the normal Excel Workbook file type (.xlsx) that I had previously saved it under. That worked. Note: I tried initially saving it as the old Excel Workbook file type (.xls) but got some compatibility/fidelity errors with pivot table features that were new to the xlsx version so be careful about going that route.

Then I went back and was finally able to save the document as the normal (.xlsx) file type.

Hope that helps!

1

I have had this problem many times; as was noted earlier, has only been seen in conjunction with a network save. When this happens I do a save as to my hard drive and it works. Then to be safe I shut down and reboot to clean up all the background RAM clutter, and I can either open the save file and resave it, or just move it to the network drive.

In my case, the "Document not saved" problem was caused by my attempting to save a spreadsheet with macro (VBA) to a network drive too quickly after doing the same thing a few seconds before (I wasn't sure if I had properly clicked Save the first time, using Excel 2000 in Windows 8.1, so I clicked it again). Windows responded by suggesting I close Excel and open the 8-letter (no extension) temp file Excel had saved, but I was fearful of losing a great deal of work so I ignored that advice. Excel saves first to a temp file and then renames that to the correct original filename, but that process was corrupted by my clicking Save (to a network drive) a second time too quickly. The result was a temp file, but my original file was gone completely from File Explorer! Scary. I deleted the temp file in File Explorer and attempted saving the still-open file to a local drive as suggested by Microsoft, but that didn't work. Excel refused to Save no matter what I tried. I was able to open another instance of Excel and copy-and-paste the data contents of my file to that, and save that as a backup (some people report that attempting to copy in this situation crashes Excel, perhaps because they have huge files.) I did not try adding the NetworkResiliency subkey to the registry as explained in Microsoft article KB230164. I then restored the temp file from the recycle bin.

Opening Excel, then from that File opening the temp file (a random 8-letter name) produced the original spreadsheet intact, after which I was able to save it successfully with a new name to a network drive.

Excel then crashed whenever I tried to open that or any .xls file. All was fine after a computer Restart however.

1

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.

3

I had the same issue, however, EXCEL would neither allow me to save the file in a different format or Copy the important (i.e. changed since last save) data to another workbook either. While I am not 100% sure, I have the impression, this happens when data was previously copied into an excel file from a different source including all formatting. So I removed all sorts of borders, colour fills from my worksheet et voila, I was able to save the file. It's certainly worth a try before loosing all work.