How to clean up corrupt styles in excel 2013

I am upgrading to excel 2013. I work with large workbooks 20mbs, 30 worksheets is about average. I get data from many sources, and many hands are in my files. I have the macro that deletes corrupt styles. However a few hundred corrupt styles will not delete using the macro or manually. The only way I have found to delete the corrupt styles is to zip the file and then delete the style portion. This deletes all formatting so then I have copy each sheets formatting in which can be tedious. In excel 2013 is there a better way to clean up the corrupt styles? I had read somewhere that Microsoft was working on this issue.

Any insight would be greatly appreciated.

it'd easier to help with an example

corrupt styles.xlsx

here is an example. All of the custom styles are corrupt, but can't be deleted.

shg,

I'm not sure what you are suggesting. I tried saving the file as an XML workbook, but I couldn't reopen it then. I did notice that the file size went from 18mb to 633mb. Might have something to do with why it wouldn't open it.

When I open the zip file I get a styles.bin not styles.xml. When I open the styles.bin with a text editor all I get is a lot of weird characters. I don't see an an xl folder or any folders for that matter.

In the past I have done something similar, where I just delete the styles.bin. When I open the file it goes through repairs, then everything is fine except all my formatting is lost. If I only had to do this once in a while it wouldn't be a big deal. Unfortunately every time someone copies in a small amount of data I pick up all of this cr** again.

Thanks for your advice!!

Yes, that worked. Save a 20mb xlsb file as a 45mb xlsx file. Then rename to zip file. Open zip file, edit styles.xml (huge text file). Update zip file, rename to xlsx file.
Open xlsx file then resave as xlsb file.

Seems a bit tedious for something I may have to do several times a week on various files. I was hoping in a newer version of excel Microsoft would fix the issue and just make it a click of a button fix.

Thanks for the help.

I use similar code on a regular basis. Excel carries over named ranges styles etc when a cell is copied and pasted from an outside workbook. I try to train my co-workers to use paste special values but that is a huge uphill battle.

Thanks

Thanks for the hint. However, the format has to be "xlsm" for it to contain a "styles.xml" file that can be edited with a text editor. The second option of "xlsb" is not valid for this. Instead, it will contain a file "styles.bin". This supposedly can be edited a hex editor, but it is not clear to me how this works. With "styles.xlm" it is easy as you see the styles delimited with tags.
My workaround was to first save the file as "...xml" from EXCEL before changing the extension to "zip".