Bizarre and frustrating bug

ndelonas

New Member
Joined
Jan 23, 2004
Messages
2
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.

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

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
Nick,

I am not nearly as proficient as you are in VBA, but the problem doesn't sound like a VBA issue. Have you tried your process on a different machine running Excel 2000? it could be as simple as a corrupt dll. (but then troubleshooting a dll is never "simple.")
 

ndelonas

New Member
Joined
Jan 23, 2004
Messages
2
Yes, I've tried it on two machines here. Also, the QA people have experienced the same problem. In fact, they were the ones who alerted me to it.

Thank you.

I think there must be some corruption on one of the sheets in the template some where. Maybe if I copied everything to a fresh workbook, it would solve the problem.

But said copying would be no small feat. This thing is huge and very complex, with hundreds of range names, print settings, etc. We're already over budget too.

My boss isn't too happy I don't think though he's been pretty good about it.

I'm hoping someone will know of way to rid a file of a hidden corruption like that. I posted this same thing on a microsoft board. Maybe someone from MS will have an idea or be able to recommend a utility.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,493
Messages
5,764,698
Members
425,231
Latest member
mramb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top