How to prevent format corruption?

Topic

New Reply

Does anyone have an idea how to prevent the format issue described on this page?

http://www.mrexcel.com/forum/excel-questions/380681-excel-2007-autorecover-changes-my-files-date-format.html

I have a very large file that crashes semi-regularly, and every time it crashes Excel asks if I’d like to attempt to recover. […] I let it recover and save as a new copy of the file. When this happens, both the original file AND the new recovery file have cell formatting changed to dates. It seems like every cell that isn’t already specifically formatted to something else is converted to date format. Cells that I have already specifically formatted to accounting, or percent, are skipped over. Everything else, including general formats, are changed to date, and date becomes the default cell formatting for new sheets created in the workbook.

I wasn’t the original poster, but it accurately describes the problem. I’d like to be able to modify a spreadsheet so that it is immune to this issue. I’ve made various attempts at prevention but nothing I try seems to work; sooner or later the format goes on the fritz. Any ideas? This is with Excel 2007 (Enterprise, if that matters), usually on Windows 7. I’ve tried using custom styles, and I’ve tried making a macro to fix the default style (which the problem appears to relate to), but Excel makes it really hard to mess with the default style.

I routinely work with complex formulas and long complex VBA apps. I don’t want to have to keep track of what format each cell SHOULD be and restore it when needed; that’s gross overkill for something which SHOULD have a relatively simple solution.

Thanks!

–Scott.

Reply | Quote

Replies

I’ve seen Excel crash in many and varied ways. The only solution I have is backup daily and revert to a backup copy when it goes pear shaped. Having “Previous Versions” running on your data volume will also give you some protection.

cheers, Paul

Reply | Quote

Thanks. I create new versions at least once every day. My issue is workbooks sent to other people. Once I’m done developing, I’d like to not have to mess with it again.

Reply | Quote

Hi Scott

Your crashing and corruption problem may be related to an ongoing ‘styles’ issue in your workbook.

The issue may involve copying/pasting data between two workbooks. In Excel 2003 you could have up to 4k styles, and after 4k you would lose formatting. With Excel 2007 – 2010 you can have 64k styles but the same corruption is there, and it can grow exponentially. If you create a new workbook and then open a corrupt workbook with 4k corrupt styles and copy from it and paste into the new workbook – new workbook has 4k corrupt styles. If you copy another cell and paste it, it goes up to 8k corrupt styles etc etc. So, as time goes on these things grow massively.

By using ActiveWorkbook.Styles.Count, you can see how many styles are in the Workbook.
I have attached a workbook that has routines to list styles and reset styles to the standard. You could perhaps try incorporating this into your workbook for testing???

zeddy

Reply | Quote

Zeddy,

Doesn’t your macro only list those styles which are named ?

I’ve run into the “excess number of styles” issue a couple of times in Excel 2003 and the problem hasn’t been the styles which are named but the number of different formats applied to cells regardless of whether they are named or not.

I had one Workbook which was teetering on the brink of the style crash disaster which had only 35 named styles.

Another one wouldn’t open at all, but I was able to rescue most of the information by opening it in OpenOffice and saving it again. That removed much of the formatting and messed with the macros but the data was at least preserved.

I’ve used an add-on to help manage styles in the past – I’ll post the details if I can find it.

But I found something better than managing excess styles “after the event”. That is to create a standard set of styles of my own and discipline myself to only use those in the construction of a new Workbook. The styles include border styles, font styles, number styles and more. I find that 40 is enough to represent whatever I want. This has a number of advantages apart from avoiding the style crash disaster:

1. It makes for a consistent look throughout a Workbook which, in turn, makes it much easier to use.
2. It makes for very fast Workbook construction.

Martin

Reply | Quote

Hi Martin

Yes. It lists styles that are named. How can you define a style if you don’t name it??
The idea is to check for ‘unexpected’ styles.
I’m not talking about cell formats.
I’m talking about corrupted bloated files that crash unexpectedly or misbehave.
For example, when the default number format spontaneously changes from General to a date format, and other reported ‘bugs’.
There are many reasons why a file can become corrupted.
For example, a workbook that has been ‘shared’ can also bloat and become very large in size and start misbehaving.
Your notes about using standard styles and discipline is very good when you are working on your own files, it’s when you have to work with files sent from other persons that can cause problems.
Much development of workbooks often starts with one that already exists (sometimes many years old) and you are ‘tuning’ and adapting it.
It’s always nice, if you have the time, to start with a ‘virgin’ workbook.

zeddy

Reply | Quote

You’re right – I was talking about cell formats. I should have said that they are also a source of possible corruption, and much harder to track down as I can’t see a means of tracking how many Excel has stored, or even what the limit really is.

Reply | Quote

Hi Martin

The format limits increased massively for versions after Excel2003.

While re-reading the original post, the fix for the sudden change of dates for the default number format is as follows:

In Excel2003
In top-panel select
Format> Style
Select Normal from Style name dropdown, then
Select: [Modify…] button
Click [Number] tab, then choose
Category: General
..then click [OK] button to reset the default number format to General

To resolve issue in Excel2007/2010:
Home>Cell_Styles
…Right-click: NORMAL…Select: Modify
…Click the Format button
…Number_Tab….Category: General

zeddy

Reply | Quote

FWIW the cell format issue is usually restricted to custom number formats – bold, italic etc don’t add anything new to the workbook.

Reply | Quote

Hi folks,

What I’m currently planning to do is implement the two fixes (reset styles and reset number formats) in my current project, so that the destination data sheets are cleaned up whenever they are saved, and maybe also when they are re-opened. Hopefully, this will result in fewer problems handling data sheets when they are modified and returned.

Thanks for the help!

–Scott.

Reply | Quote