Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Corrupt Styles (Excel 2003 SP 1)

    Does anyone know of a way to delete corrupt Excel styles?

    I have a large workbook (about 60 sheets) that had almost 7,000 names, more than half of which had #REF errors. I used Jan Karl Peterse's Name Manager to get rid of them (including about 80 that needed to be renamed one at a time). I then deleted all the unused custom number formats (about 150). However, when I tried deleting the styles, the macro I used failed. I found a total of almost 1,400 styles, and the macro checks which are in use and deletes the others. It managed to delete almost 300 before it failed. I then tried using the ASAP Utility macro but it too failed. I have another macro that will delete all styles not defined as NORMAL, but that is brute force, and although it doesn't fail it doesn't delete one particular corrupt style.

    The style in question is ='C':WINNT35SYSTEM32COMMAND.COM, which is how it shows up in the Style dialog, and how it shows up in the variables, but there doesn't seem to be any way to delete it.

    I also tried saving the file in XML format and opening it Notepad to see if I could find the style that way, but that didn't work either.

    Then it occurred to me that I should ask here. If there's a solution to be had, this is the place that will have it.

    Thanks for any input...

  2. #2
    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

    Re: Corrupt Styles (Excel 2003 SP 1)

    Hi,
    I would suspect that your entire workbook may be corrupt rather than just a particular style. I can't imagine anyone naming a style <code>'C':WINNT35SYSTEM32COMMAND.COM</code> which is the name and path of the program to launch a command prompt. I have also never seen a workbook with anything like 1400 styles, 7000 names and 150 custom number formats. If it actually had all those, I confess I'm not entirely surprised that it ended up corrupt!
    I would suggest you either try copying the individual sheets to a new workbook (checking after each one to see if the new book is ok) or try copying just the data to a new book. (I am assuming that it is only this one workbook you are having the problem with.)
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Corrupt Styles (Excel 2003 SP 1)

    Another way is to save the workbook as HTML and look for the style name in there. Then edit the name to a legitimate one might resolve the problem.

    BTW: I do have a utility to automatically rename corrupt names (Now he tells me! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Corrupt Styles (Excel 2003 SP 1)

    Hi,

    Thank you for your response.

    Unfortunately, I work in an environment (large financial instution) where such files are commonplace. I use Jan Karl Peterse's magnificent Name Manager regularly to ferret out the thousands of names I see regularly. In fact, when I was a consultant I found that to be true in many similar institutions.

    I have seen all too many files with styles that have similar and weirder names, and similar numbers of custom number formats.

    And it generally isn't feasible to copy the worksheets as you suggest for a number of reasons: some books have more than 50 sheets, some have links that point within and without, some have very hidden sheets created by third-party add-ins, etc.

    It's a MAJOR headache that Microsoft refuses to acknowledge.

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Corrupt Styles (Excel 2003 SP 1)

    Hi Jan,

    I will try the HTML approach; great suggestion.

    And if I could get your utility I would be in heaven. It is murder manually changing 75 or 100 names only to have Excel crash before I can save (or if I <gulp> forget to save).

    Thank you for all the great stuff you do in the Excel world...

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Corrupt Styles (Excel 2003 SP 1)

    Well, the utility is not a give-away.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Corrupt Styles (Excel 2003 SP 1)

    Well, given that I've gotten so much use from your free utilities, I would feel that it would be both shortsighted and, yes, cheap of me not to at least inquire further.

    Of course, both price and the scope of the utility will be factors, but please let me know more...

    Regards,

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Corrupt Styles (Excel 2003 SP 1)

    >I have also never seen a workbook with anything like 1400 styles, 7000 names and 150 custom number formats.

    This is fairly inevitable when assembling reports through various offices/locations. I used to use custoim code to clean up such workbooks to reduce the possibility of their corrupting after they left my hands - although such code did not specifically deal with corrupt styles.
    Gre

  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

    Re: Corrupt Styles (Excel 2003 SP 1)

    I guess I've just led a sheltered life! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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