5

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
  • 3
    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, 2010 at 20:15
  • 1
    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. Sep 6, 2011 at 14:57

8 Answers 8

4

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.

2

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
  • Simply opening another instance of Excel and closing it solved my problem. Thank you so much for that tip! Jun 27, 2016 at 16:10
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 managed to get this to work... Once, to salvage one specific sheet with particularly useful data. It's not really practical for complex workbooks with formulas across multiple sheets and large numbers of named ranges. @D.X.M. - I'll leave your answer up, as it is at least a practical suggestion, even if it doesn't explain or resolve the issue completely. Mar 14, 2016 at 14:34
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
  • Looks useful. I'll hold off tagging this as the 'Answered' answer until I get the error again, and can test that this actually works. Jul 9, 2015 at 13:27
0

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.

0

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
  • Thanks Max, that's one of the better answers - I think you were lucky, as you won't always get a visible temp file - But I'm not sure that you're describing exactly the same problem I got: did you see a 2Tbyte temp file, or was this just an ordinary failed 'save' ? Mar 14, 2016 at 14:43
0

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
  • Did you mean to comment and give advice for your own post?
    – Dave
    Oct 26, 2012 at 14:24
  • @Dave - There's enough information for it to be more than a comment; it's an answer, but not the full answer. Meanwhile, answers from deleted users now appear under the name tag of the question's author... Me. This is embarrassing, as some of those answers are 'not even wrong'. Worse, I have an admin adding the same errors in edits to this (partial) answer. Mar 14, 2016 at 14:28
  • You do know I wrote that comment in 2012 :) Also, the reason they are deleted is because this is a Q and A site, where as your posts (the deleted ones) are not really 'answers', they are more "updates". If you feel any part of your posts are answers (even part answers) then you can copy them into new answers here but usually people put many suggestions into 1 answer :)
    – Dave
    Mar 14, 2016 at 15:27
0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .