Bizarre and frustrating bug

[NJD]

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.

 

[Keith R]

I had a similar problem in XL97, which was never resolved

Have you tested without any passwords (on sheets or VBA)? I know
your final solution may require VBA protection, but that's my first
thought as to something to test.

Does the Bovey code cleaner work in XP? I'd try that, regardless.


I'm not a heavy user of pivot tables, but are there any features
of XP pivot tables that were not available in 2000 that might
cause problems? If so, such problems might only be seen when
data is present, thereby "triggering" those features. It might be
worth taking a clean workbook and building one pivot table
(similar to the most complex one you have in your workbook,
in terms of data complexity, formatting, etc) to test in 2000.

If you just want your output, I'm not sure it would be that difficult
to copy each sheet, and it's print settings, unless you have a lot
of inserted page breaks. I always found hard page breaks to be
a problem in XL, so I quit using them altogether. Can you be more
descriptive about how many sheets/pages you have, or why you
forsee problems using that as a solution? I'm thinking along the
lines of (select sheet) copy/paste special, paste values. If
the formatting is critical, I sometimes paste first (which includes
formatting, but also the formulas, etc) then repaste on top of that
the paste special/values to get rid of the formulas. You could
always test a few of your more complex sheets first, then set
up VBA to just cycle through all sheets and transfer to a new
workbook.

HTH,
Keith

 

[NJD]

I had a similar problem in XL97, which was never resolved

Hoo boy.

Have you tested without any passwords (on sheets or VBA)? I know
your final solution may require VBA protection, but that's my first
thought as to something to test.
Yes.

Does the Bovey code cleaner work in XP? I'd try that, regardless.

I did. No effect.

I'm not a heavy user of pivot tables, but are there any features
of XP pivot tables that were not available in 2000 that might
cause problems? If so, such problems might only be seen when
data is present, thereby "triggering" those features. It might be
worth taking a clean workbook and building one pivot table
(similar to the most complex one you have in your workbook,
in terms of data complexity, formatting, etc) to test in 2000.

I'm going to try a few other things first. I will never use pivot
tables in a project of this kind again. All it did was introduce a
black box that saved me a little time up front. I really wish I could
go back, take a few extra days and do the same tasks using data filters
and VBA code instead.

Data filters are much faster than pivots any way I think.

Actually the only reason I started using the pivots is because that's
the way the clients got their information manually. It was a mistake,
though I'm not certain they are the problem.

If you just want your output, I'm not sure it would be that difficult
to copy each sheet, and it's print settings, unless you have a lot
of inserted page breaks. I always found hard page breaks to be
a problem in XL, so I quit using them altogether. Can you be more
descriptive about how many sheets/pages you have, or why you
forsee problems using that as a solution?

Something like forty or so sheets IIRC. Can be more or less depending
on user choices and data. There are many thousands of lines of code and
very complex formulas. This one was a real brain bender of a project.
I can't believe I got through all the complex stuff and now I'm stopped
cold by THIS! The model works perfectly, but I can't save it?!

It's like I'm in a black comedy or something.

As for copying, the main problem would be the charts I think. There are
a ton of charts in this thing and they're positioned rather precisely
for printing.

I hate dealing with charts through VBA. They are quite buggy IMO.

I'm thinking along the
lines of (select sheet) copy/paste special, paste values. If
the formatting is critical, I sometimes paste first (which includes
formatting, but also the formulas, etc) then repaste on top of that
the paste special/values to get rid of the formulas. You could
always test a few of your more complex sheets first, then set
up VBA to just cycle through all sheets and transfer to a new
workbook.

I intend to pursue this very path tomorrow Keith and I thank you for
your input. If you've got any suggestions on how to handle copying and
positioning charts accurately, I'd be in your debt. I am considering
copying everything else, but moving the pages with charts and keeping my
fingers crossed. (But the way my luck has been going . . .)

I spent six frustrating hours this morning using a variety of utilities,
including Microsoft's clean XLA with no luck at all. I then got the
idea that the problem might have something to do with the formatting.

It does!

Here's what I've since determined.

1) I can run and save the model, as long as there is no formatting on
any of the pages. It works in Excel 2000 as is, pivot tables and all.

2) I can run the model. Then if I remove the data, leaving the
formatting in tact, that will also work in XL 2000.

So basically the model works great as long as I don't want to save any
useful output! LOL! What a life I've got!

It is apparent that for some reason, if I keep both the data and the
formatting, Excel 2000 thinks that quite a few of the pages contain
information in every single cell. XP does not. That is why when I load
the saved file in 2000, CPU goes to 100% and the machine eventually runs
out of resources.

Most frustrating bug/programming challenge I've ever faced.

 

[Keith R]

One (or two) other questions;
Do your customers require the ability to "manipulate" the
output file, or do you just need to produce a copy they
can read? If so, that would open the option of printing
everything to a PDF from XP, which is pretty easy to
automate- as long as the production work is
centralized...if you needed multiple copies of Adobe
then it starts getting pricey as a "work-around" solution...

Also, have you narrowed down what formatting seems
to be causing the problem? What kinds of formatting
are you using on the pages that cause the problems?

Thanks,
Keith R

 

[NJD]

One (or two) other questions;
Do your customers require the ability to "manipulate" the
output file, or do you just need to produce a copy they
can read?
Manipulate.


Also, have you narrowed down what formatting seems
to be causing the problem? What kinds of formatting
are you using on the pages that cause the problems?

No. It's obviously some kind of weird bug deep in the bowels of Excel
2000.

I did have some success today however with a workaround. I went ahead
and copied pages and formats to a new workbook and moved the pages
containing charts. While I've still got some VBA bugs to work out, the
resulting file can be opened in Excel 2000 with no problem.

What I've got to deal with now is that Excel refuses to keep moving
pages with charts at one point in the loop. I have no idea why.
Tomorrow I'll move all the chart pages as an array and then rearrange in
the new workbook as necessary. That should work.

I've already got it so that the macro copies all row and column heights
and widths. That was easy. I'll also have to copy the individualized
page print setups. That won't be hard, but it's going to be painfully
slow with so many pages. (I'll test that last and hope the users don't
complain too much.)

The biggest challenge will be removing the links in the chart data
sources to the originating workbook. I've never done that before but
hopefully it won't be too bad.

Thanks for your suggestions. Your post yesterday convinced me to move
ahead with the copying and I don't think it'll take as long to code as I
had feared. Hell, writing code to copy the column and row sizes only
took a few minutes.

It's the chart links that worry me, but I'm a thousand miles better off
than I was yesterday at this time. Now I know it's going to work.
We've gone over budget, but the project will be delivered and my
understanding is that it will still provide a very healthy return on
investment.

I should be able to sleep tonight for a welcome change.

Thanks again.

 

[Dave Peterson]

You may want to look at Jon Peltier's page for delinking the source from a
chart:

http://peltiertech.com/Excel/Charts/chartmisc.html#DelinkCht
which takes me to:
http://peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html

 

[NJD]

You may want to look at Jon Peltier's page for delinking the source from a
chart:

http://peltiertech.com/Excel/Charts/chartmisc.html#DelinkCht
which takes me to:
http://peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html

Thanks Dave. I might do it that way. My first inclination was to use a
macro to edit the reference out of the link since the new workbook will
contain identically named worksheets with data in the same relative
positions. That might even be an easier approach.

I'll let you know.