Charts corrupted, cannot save file

Question

After many months of creating charts with VBA in a file, I am now not able to save the file with the 3 (sometimes 4) charts in the workbook.

The error message I get is "Errors were detected while saving [file Name]. Microsoft Excel may be able to save the file by removing or repairing some features." And offer to continue or cancel the save. If all the charts are removed, the file saves without problems. I was converting to use templates and thought this was the problem. But I converted back to my VBA-only subroutines and still get the problem. The problem must be in one or more of the Charts?

I also occasionally when the charts are present get an error message "An unexpected error has occurred  and Autorecovery has been disabled for this session of Excel". After this occurs the file can not be saved and the only options is to exit Excel or close the file without saving the file and reload the file lossing all work during the session.

I assume these error are related (same problem)? Can anyone offer methods to troubleshoot and identify this problem and eliminate it?

Answer 1

Thanks for posting your solution! It may well help others.

Answer 2

See http://www.jkp-ads.com/Articles/CorruptFiles.asp.

Answer 3

I have solved the problem.

In updating charts, I did not set the xvalues (i.e., activechart.SeriesCollection(1).xvalues = ""Sheet 1'!A2:A100") ) for all seriescollections. I only set it for the 1st seriescollecion of each Y axis (Primary and secondary). The chart causing the problem has 8 seriescollections (4 primary axis and 4 secondary axis).

I set the .xvalues for all seriescollections and have not experienced the autorecovery error or the file save problem since.

The x-axis for these charts are dates and Excel appears to match dates set by the .xvalues = statement to the axis dates and skip blank dates. I can not find much help or documentation on how this works. So time-consuming trial and error is required to figure how to best use or workaround.

Answer 4

I was hoping there is a method to isolate the problem and eliminate it. I already know how to repair corrupt files and have done so in this case. Until recently, this problem did not occur and I was able to save the file with the charts in it.

Thanks for your support.