Corrupt Styles (Excel 2003 SP 1)

Topic

New Reply

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…

Reply | Quote

Replies

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 'C':WINNT35SYSTEM32COMMAND.COM 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.)

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

I guess I’ve just led a sheltered life!

Reply | Quote

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! )

Reply | Quote

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 forget to save).

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

Reply | Quote

Well, the utility is not a give-away.

Reply | Quote

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,

Reply | Quote