I'm facing the most bizarre and frustrating bug that I've seen in 15
years of programming. I hope someone here can offer a solution.

For several months, I've been working full time on a large (12.5 MB) and
complex Excel model (template) that produces a variable number of
reports, each on a separate worksheet. The number of reports/pages
created depends on user selections and on data retrieved. The model
requires use of Excel 2000 or later. I have developed and tested using
both Excel 2000 and Excel XP.

The very first thing I created in this model was a VBA routine that
clears out all imported data, resizes data-base range names to two rows
and then saves the template sans data. That has always worked.

About mid way in the project I added four pivot tables, with the last
three all using the cache of the first. Since the template has no data,
on data import, VBA refreshes all four pivot tables.

A while after that I started having trouble with the template being
corrupted and losing the pivot tables when reopened. So I refreshed the
pivot tables after clearing out the data just before saving the
template. That solved the problem.

Okay, now the model is done. All the reports have been thoroughly
tested, the print settings all work and the many charts are all
formatted correctly. It is essentially finished with one, what should
be small exception. The must be able to save the report for use by
clients outside the company.

But no matter what I try, I cannot get Excel to save the workbook in a
way that is readable by Excel 2000. Excel XP can read the saved files
with no problem, but Excel 2000 can not.

I've tried many different ways to save the model for the client that all
work fine in Excel XP but only in Excel XP. All the VBA code is legal
for Excel 2000. It compiles in Excel 2000. It even runs in Excel 2000.
BUT, the resulting file once saved, cannot be read by Excel 2000.

Okay now it gets weird.

At the end of any of the save procedures I've tried, you get an Excel
file that looks perfectly normal. In Excel XP, it behaves normally.
You can save it. You can retrieve it.

Though this also happens in 2000, you can never retrieve the file after
it's been saved. After the macro is done running and the file that
contained it is closed, you're left with a file containing only values,
labels and charts (formulas converted to values). Even if the user
manually saves this seemingly normal looking file, the saved files can
never be retrieved by any version of Excel other than XP. Even if you
choose to save the file in an older Excel format, only XP can read it.
This is the case whether the VBA macros are compiled in either Excel XP
or Excel 2000.

And it gets even weirder.

There seems no way to rehabilitate the saved file. Even if I restart my
machine open the file in XP and save it to a NEW file name, Excel 2000
will never be able to read it. Yet XP can read it with no problem.

I can specify that the file be saved as an Excel 97 file or even an
Excel 95 file. It saves fine. XP can reload the saved file with no
problem, but Excel 2000 can not.

So again, this has got to be the weirdest damned bug I've ever seen and
every solution I try works fine in XP, but fails in 2000. It doesn't
seem to be a memory problem because I installed Excel XP on the oldest
computer I own and though it took forever to run, everything worked
fine, but only in XP!

Excel 2000 will start to retrieve the file, but at about 10% on the
progress bar, CPU usages goes to 100% and I have to use Task Manager to
end the Excel process.

Here are things I've tried that did not work.

I left the file as it is with all formulas and VBA code, but just locked
a few things -- hid some pages and disallowed viewing the VBA macros.
Still won't save, unless I clear out all the data and refresh the pivots
as I've been doing all along, but which defeats the purpose.

I've tried converting all formulas to values, deleting the pivot tables
in the reverse order I had created them and then deleting all hidden
pages. No luck. Still can't retrieve saved file.

I then tried adding a new workbook, moving the pages from the original
template so that the resulting file would contain no formulas, no hidden
pages and no VBA code. Same thing. It opens up fine in XP but will not
open in Excel 2000.

Obviously there is some kind of corruption that only becomes evident
when the model has data and is only a problem for older versions of
Excel. I've thought of trying to manually copy everything to a new
workbook to see if that solves it, but that would be a big job. There
are hundreds of range names, complex formulas, all kinds of charts,
print settings for each sheet, etc. I'm not even certain that would
work.

Anyone have any ideas? This is a rather serious matter.
People have been working on this project for months.

--
Nick Delonas