Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    May 2014
    Posts
    45
    Thanks
    0
    Thanked 2 Times in 2 Posts

    How to prevent format corruption?

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

    http://www.mrexcel.com/forum/excel-q...te-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.

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,170
    Thanks
    47
    Thanked 980 Times in 910 Posts
    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

  3. #3
    Lounger
    Join Date
    May 2014
    Posts
    45
    Thanks
    0
    Thanked 2 Times in 2 Posts
    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.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    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
    Attached Files Attached Files

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    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

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    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

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    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.

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    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

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FWIW the cell format issue is usually restricted to custom number formats - bold, italic etc don't add anything new to the workbook.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Lounger
    Join Date
    May 2014
    Posts
    45
    Thanks
    0
    Thanked 2 Times in 2 Posts
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •