Long Term Excel Bug ("Errors were detected while saving...")

[ColinBurrows]

For the longest time (probably ~15 years!) I have sporadically encountered the bug where you cannot save a workbook. The "Errors were detected..." message is followed by "Document not saved", after which you have to close the workbook, losing all your updates. Very frustrating, even more so because I have never been able to reliably replicate the error. UNTIL NOW...
Try the following in the attached workbook. Open the file, click on a cell within the fData table, and create a new pivot table. Whether you add fields or not, if you then try to Save the file (or Save As) you'll get the error.
There's an existing pivot table with the same data source, so presumably that's part of the puzzle. You're creating a separate cache, but that should be allowed.
The interesting thing I found is that if you delete the inserted sheet, then repeat the steps, it works! Which means that it must have reset how the memory is configured.
I'd be interested if the error does NOT occur for anybody.

Also, maybe the moderator could refer this to the man himself(?). I imagine Bill would be interested and might want to do a video on it.

P.S. Have written all the above, but now it seems that I can't attach a workbook? Is that true??

 

[Anthony47]

You have to use a filesharing service, available for example in dropbox, googledrive, onedrive and many other servers

 

[MARK858]

P.S. Have written all the above, but now it seems that I can't attach a workbook? Is that true??

Yes, it is stated in the boards FAQ's

MrExcel Message Board FAQ

Welcome to the MrExcel Message Board!

www.mrexcel.com

and the Board's guidelines

Guidelines

Guidelines for MrExcel Message Board Use

www.mrexcel.com

In your case as @Anthony47 has stated upload your file to a free file hosting site (like www.box.com or www.dropbox.com), mark the file for sharing and post the link it creates in the thread or use your Onedrive.

 

[ColinBurrows]

For the longest time (probably ~15 years!) I have sporadically encountered the bug where you cannot save a workbook. The "Errors were detected..." message is followed by "Document not saved", after which you have to close the workbook, losing all your updates. Very frustrating, even more so because I have never been able to reliably replicate the error. UNTIL NOW...
Try the following in the attached workbook. Open the file, click on a cell within the fData table, and create a new pivot table. Whether you add fields or not, if you then try to Save the file (or Save As) you'll get the error.
There's an existing pivot table with the same data source, so presumably that's part of the puzzle. You're creating a separate cache, but that should be allowed.
The interesting thing I found is that if you delete the inserted sheet, then repeat the steps, it works! Which means that it must have reset how the memory is configured.
I'd be interested if the error does NOT occur for anybody.

Also, maybe the moderator could refer this to the man himself(?). I imagine Bill would be interested and might want to do a video on it.

P.S. Have written all the above, but now it seems that I can't attach a workbook? Is that true??

This is the link:

v08.xlsx

Shared with Dropbox

www.dropbox.com

 

[Anthony47]

I got curious about this problem and tried to drill the file down, but with little result
I focused on the fData table on the Data sheet, because the file performs well if a pivot table is generated from other tables or from other sources.
I looked for the small differences between the descriptive files of the working / not working files, but I am not documented about how to interpret the meaning of the various attributes, thus I didn't solve much.
In particular I found it strange that in sheet1.xml (the description of the DATA sheet) of the original file the description of column E is missing, and that the table columns are marked 1, 3-11 and not 1-10

I noticed that by deleting and recreating this table the problem does no longer occur, ie the file can be saved even after creating a pivot based on this table. For this double conversion:
select the table, use the Convert to Standard Range command; immediately after, with the same range still selected, use the command “Insert a table, with headers”.
I had to re-edit the formulas in columns I-J-K so that they refer again to the table fields and not generically to the sheet cells.
This led me to the hypothesis that the table is somewhere corrupted, but I was not able to pinpoint anything specific.

Indeed I made a lot of experiments, most of them quite naïve, but only recreating the table I got some useful feedback.
This investigations was done opening the .xlsx file with 7Zip, then drilling down in the folders and files that are behind the xlsx internal structure; "tables" description is in the XL folder.

 

[ColinBurrows]

Interesting - thanks for the response. My hypothesis is that it's not the table per se, but some memory glitch related to a previously created pivot table. If you create the pivot table, then delete it, it should be that you've returned everything to the original state when you opened the file. But not so. The act of creating then deleting the pivot table fixes the problem, because you can then (re)create a pivot table and save it successfully. So, in my mind the problem centers around the saving of the file. It was somehow saved in an unstable state (kind of), in that it allows the problem to manifest itself.
My interest in all this is not that I have a problem that needs fixing. Since I (finally!) have a workbook that can be used by Microsoft to fix the problem, I was hoping to get it to them. Previously they have never been able to help me. I spoke to their support line, but couldn't get high enough on their food chain for it to be of any help.

 

[Anthony47]

My hypothesis is that it's not the table per se, but some memory glitch related to a previously created pivot table.

Probably you are right
Indeed one other action that makes you workbook "well behaving" is updating the pivot table on sheet pvtElim

Microsoft have a bug reporting process, did you try through that channel?

 

[ColinBurrows]

Well, I think we're both right. When I first created the table I had Year and Month as the first 2 columns. But I replaced them with a single date column (because I wanted to use a Timeline instead of slicers). If that's what's going on, then it would explain why it happens so rarely.
Thanks for exploring(!). I'll try to report it to Microsoft.