Too Many Cell Formats Error

[ZTamsen]

I have read the various discussion threads on the "Too Many Cell Formats"
error, but they don't help me. I am stuck in Office 03 and can't switch
around to Open Office. Also, and this is they key, I'm working in documents
with FAR FEWER than 4000 formats. For example, I get the error on a small,
single sheet document that I produce weekly. I build each week from the one
used the prior week, but only update the numbers. It feels like a bug or
corruption issue I'm having.

Any suggestions would be extremely helpful.

Thanks.
zt

 

[FSt1]

hi
i have ran into your problem but not with the too many formats. It has
something to do with constantly re-using the same file over and over. for
some reason each time you reuse the file, it seem to carry extra baggage over
to the new file. not sure what causes it. in my case after re-using the same
file over and over, it just starts to acting weird, not responding to icons,
displaying odd data in strange places, etc. the only solutions i have come up
with is just to start over with a new workbook from scratch.
also understand that just becasue a cell doesn't have data in it, doesn't
mean that the cell doesn't have formating. try high lighting all column to
the left of your data.
set number format to general. set text fonts to your standard. do the same
with all of your rows below your data. save the file. if that don't do, may
be time to start over with a newly created workbook.

regards
FSt1

 

[Simon Murphy]

If its a single sheet, you could just rebuild it by copy pasting the
formulas and then manually do the formats.

This confirms the issue, but adds little useful info:
http://support.microsoft.com/kb/213904

commercial tools:
http://www.rowingservice.com/quarrell/QAid/
http://xlsgenreduction.arstdesign.com/index_en.html

Check the number of styles (Format>>Style, in the top drop down)
If there are more than say a few hundred styles then this might help

sub delstyles
dim s as style
for each s in activeworkbook.styles
if not s.builtin then s.delete
next s
end sub
(not tested - but shouldn't be far off)

Cheers
Simon
Excel development website: www.codematic.net

 

[XLGeek]

Many people seem to run into this problem in both 2003 and 2007. In Excel
2007 most often the issue is related to the exessive number of unused often
corrupted styles and not so much cell unique cell format combos. I wrote
a utility to fix XL2007 OOXML files that can also be saved down to XL2003 to
specifically remove corrupted styles.
Here is the link to the blog post:
http://sergeig888.spaces.live.com/blog/cns!53E1D37F76F69444!534.entry

Requres .Net3.5 and MS Excel 2007. Will fix xlsx or xlsm files. Won't work
on binary xlsb or password protected files (read-only passwords are OK).

No need to run the risk of further corrupting your file by using Open Office
like some people recommend in other Excel forums. Utility is free and comes
"as is".