Lost Format Error

I have a worbook that open fine in Excel 2000. When opened in Excel 2003,
it pops up a dialog with this error message:

"File error. Some number formats may have been lost!"

I can't seem to find any formats that are gone. But the book has many, many
sheets and is complicated, so I am not sure that the book is still OK. I
am reluctant to simply click OK and save the file. I am afraid this may be
a symptom of other problems. Before exploring the other problems, I wanted
to learn more about the lost formats problem.

Questions:
Are there formats that are legal in Excel 2000, but illegal in Excel 2003?
Is the maximum number of custom formats less in 2003 than in 2000?
Is there a way to write a macro to list all formats employed (or available)
in a Workbook?

John

John,

1. I don't have XL2003, however I am not aware of any cell formats that are
not interchangeable between Excel versions.

2. http://support.microsoft.com/default...b;en-us;213904
"You receive a "Too many different cell formats" error message in Excel"
The above explains that there is a limit of about 4000 different formats in a workbook.

3. Listing all of the formats would be very difficult to do.
The message you refer to does mention "number" formats and those you can
list including the unused ones.

I would make a backup copy of the workbook using Windows Explorer
and then try the "Open and Repair" option. That is found in the File Open
dialog box by going to the little down arrow adjacent to the Open button in the
bottom right corner of the dialog box.

If that works, I would still look at reducing the number of formats in the workbook.
If it doesn't work (or even if it does), I can provide a free Excel add-in that
lists and/or removes all of the unused "Custom Number Formats" from
a workbook. If that interests you, send me an email request after removing XXX
from my email address.

Regards,
Jim Cone
San Francisco, USA
[email protected]XX



"John F. Collins" <[email protected]> wrote in message news:[email protected]...
I have a workbook that open fine in Excel 2000.
When opened in Excel 2003, it pops up a dialog with this error message:
"File error. Some number formats may have been lost!"
I can't seem to find any formats that are gone. But the book has many, many
sheets and is complicated, so I am not sure that the book is still OK.
I am reluctant to simply click OK and save the file. I am afraid this may be
a symptom of other problems. Before exploring the other problems,
I wanted to learn more about the lost formats problem.
Questions:
Are there formats that are legal in Excel 2000, but illegal in Excel 2003?
Is the maximum number of custom formats less in 2003 than in 2000?
Is there a way to write a macro to list all formats employed (or available)
in a Workbook?
John

Thanks. I will try the Open and Repair option.

Too many format combinations could be the problem. Whoever made this
workbook used formats instead of labels. I.e., instead of puting labels in
one column and numbers in an adjacent column, they used formats like this:

"Gasoline: $" 0.00
"Food: $" 0.00
"Clothing: $" 0.00
"Number of angels on the head of a pin is " 0.0E+000

Only not so simple. The actual spreadsheet is a complicated set of chemical
measurements and model predictions occupying about two dozen pages. Despite
the insane overuse of formating, XL2000 does not offer any complaint.

I will e-mail you for the add-in. I'd like something simpler than an add in
if you have it. Like a NumberFormats Collection property of the workbook,
or something like that?

John

"Jim Cone" <[email protected]> wrote in message
news:[email protected]...
> John,
>
> 1. I don't have XL2003, however I am not aware of any cell formats that
are
> not interchangeable between Excel versions.
>
> 2. http://support.microsoft.com/default...b;en-us;213904
> "You receive a "Too many different cell formats" error message in Excel"
> The above explains that there is a limit of about 4000 different formats
in a workbook.
>
> 3. Listing all of the formats would be very difficult to do.
> The message you refer to does mention "number" formats and those you can
> list including the unused ones.
>
> I would make a backup copy of the workbook using Windows Explorer
> and then try the "Open and Repair" option. That is found in the File Open
> dialog box by going to the little down arrow adjacent to the Open button
in the
> bottom right corner of the dialog box.
>
> If that works, I would still look at reducing the number of formats in the
workbook.
> If it doesn't work (or even if it does), I can provide a free Excel add-in
that
> lists and/or removes all of the unused "Custom Number Formats" from
> a workbook. If that interests you, send me an email request after
removing XXX
> from my email address.
>
> Regards,
> Jim Cone
> San Francisco, USA
> [email protected]XX
>
>
>
> "John F. Collins" <[email protected]> wrote in message
news:[email protected]...
> I have a workbook that open fine in Excel 2000.
> When opened in Excel 2003, it pops up a dialog with this error message:
> "File error. Some number formats may have been lost!"
> I can't seem to find any formats that are gone. But the book has many,
many
> sheets and is complicated, so I am not sure that the book is still OK.
> I am reluctant to simply click OK and save the file. I am afraid this may
be
> a symptom of other problems. Before exploring the other problems,
> I wanted to learn more about the lost formats problem.
> Questions:
> Are there formats that are legal in Excel 2000, but illegal in Excel 2003?
> Is the maximum number of custom formats less in 2003 than in 2000?
> Is there a way to write a macro to list all formats employed (or
available)
> in a Workbook?
> John
>
>

Open and Repair does not help. The file opens without reporting or asking
about repairs, then pops up the "File error. Some number formats may have
been lost!" dialog, same as before.

After reading about"too many different cell formats", I am now less worried
about corruption of the file. There are limits, and losing some formats is
probably benign.

I'll report back about results using the Add-in once I try it.

John

"Jim Cone" <[email protected]> wrote in message
news:[email protected]...
> John,
>
> 1. I don't have XL2003, however I am not aware of any cell formats that
are
> not interchangeable between Excel versions.
>
> 2. http://support.microsoft.com/default...b;en-us;213904
> "You receive a "Too many different cell formats" error message in Excel"
> The above explains that there is a limit of about 4000 different formats
in a workbook.
>
> 3. Listing all of the formats would be very difficult to do.
> The message you refer to does mention "number" formats and those you can
> list including the unused ones.
>
> I would make a backup copy of the workbook using Windows Explorer
> and then try the "Open and Repair" option. That is found in the File Open
> dialog box by going to the little down arrow adjacent to the Open button
in the
> bottom right corner of the dialog box.
>
> If that works, I would still look at reducing the number of formats in the
workbook.
> If it doesn't work (or even if it does), I can provide a free Excel add-in
that
> lists and/or removes all of the unused "Custom Number Formats" from
> a workbook. If that interests you, send me an email request after
removing XXX
> from my email address.
>
> Regards,
> Jim Cone
> San Francisco, USA
> [email protected]XX
>
>
>
> "John F. Collins" <[email protected]> wrote in message
news:[email protected]...
> I have a workbook that open fine in Excel 2000.
> When opened in Excel 2003, it pops up a dialog with this error message:
> "File error. Some number formats may have been lost!"
> I can't seem to find any formats that are gone. But the book has many,
many
> sheets and is complicated, so I am not sure that the book is still OK.
> I am reluctant to simply click OK and save the file. I am afraid this may
be
> a symptom of other problems. Before exploring the other problems,
> I wanted to learn more about the lost formats problem.
> Questions:
> Are there formats that are legal in Excel 2000, but illegal in Excel 2003?
> Is the maximum number of custom formats less in 2003 than in 2000?
> Is there a way to write a macro to list all formats employed (or
available)
> in a Workbook?
> John
>
>

John,

From your description, too many custom numbers formats might
be the major contributor to the problem. Unfortunately, there is no
NumberFormats collection or anything close to it.

Leo Heuser is the king of custom number format code. He wrote the
"original" routine back in 1999 and has made updates over
the years. Every thing I have seen, re number format codes, is based
upon his code (including mine). It is complicated code.
My add-in does provide an easy interface and a nicely laid out listing
of the number formats and of course I have "improved" Leo's code. <g>

Regards,
Jim Cone


"John F. Collins" <[email protected]> wrote in message news:[email protected]...
Thanks. I will try the Open and Repair option.

Too many format combinations could be the problem. Whoever made this
workbook used formats instead of labels. I.e., instead of puting labels in
one column and numbers in an adjacent column, they used formats like this:

"Gasoline: $" 0.00
"Food: $" 0.00
"Clothing: $" 0.00
"Number of angels on the head of a pin is " 0.0E+000

Only not so simple. The actual spreadsheet is a complicated set of chemical
measurements and model predictions occupying about two dozen pages. Despite
the insane overuse of formating, XL2000 does not offer any complaint.

I will e-mail you for the add-in. I'd like something simpler than an add in
if you have it. Like a NumberFormats Collection property of the workbook,
or something like that?

John

"Jim Cone" <[email protected]> wrote in message
news:[email protected]...
> John,
>
> 1. I don't have XL2003, however I am not aware of any cell formats that
are
> not interchangeable between Excel versions.
>
> 2. http://support.microsoft.com/default...b;en-us;213904
> "You receive a "Too many different cell formats" error message in Excel"
> The above explains that there is a limit of about 4000 different formats
in a workbook.
>
> 3. Listing all of the formats would be very difficult to do.
> The message you refer to does mention "number" formats and those you can
> list including the unused ones.
>
> I would make a backup copy of the workbook using Windows Explorer
> and then try the "Open and Repair" option. That is found in the File Open
> dialog box by going to the little down arrow adjacent to the Open button
in the
> bottom right corner of the dialog box.
>
> If that works, I would still look at reducing the number of formats in the
workbook.
> If it doesn't work (or even if it does), I can provide a free Excel add-in
that
> lists and/or removes all of the unused "Custom Number Formats" from
> a workbook. If that interests you, send me an email request after
removing XXX
> from my email address.
>
> Regards,
> Jim Cone
> San Francisco, USA
> [email protected]XX
>
>
>
> "John F. Collins" <[email protected]> wrote in message
news:[email protected]...
> I have a workbook that open fine in Excel 2000.
> When opened in Excel 2003, it pops up a dialog with this error message:
> "File error. Some number formats may have been lost!"
> I can't seem to find any formats that are gone. But the book has many,
many
> sheets and is complicated, so I am not sure that the book is still OK.
> I am reluctant to simply click OK and save the file. I am afraid this may
be
> a symptom of other problems. Before exploring the other problems,
> I wanted to learn more about the lost formats problem.
> Questions:
> Are there formats that are legal in Excel 2000, but illegal in Excel 2003?
> Is the maximum number of custom formats less in 2003 than in 2000?
> Is there a way to write a macro to list all formats employed (or
available)
> in a Workbook?
> John
>
>