workbook opening takes 2 minutes

[Harry]

Hey all:

I have a workbook that takes 2 minutes to open once I enable macros.
The macros are not workbook event driven (they are unrelated to the problem).

I think the problem stems from an excessive # of pivot tables and pivot
charts. I have 25 pivot tables, all using the same source data (the data is
sliced and diced in different ways).

I am also using a lot of database functions (DAVERAGE, DCOUNT, DCOUNTA) but
I don't think this is adding to the problem.

I am using a few functions from the analysis toolpack (NETWORKDAYS is the
big one). The analysis toolpak is showing up as an external link (user is
prompted to update links when workbook is open).

Finally, I have a lot of VLOOKUPS that are essential to pulling data and
putting it into charts,

Can anyone offer some guidance? I am looking at knocking down the workbook
opening time (2 minutes is too long).

thanks,
Harry

 

[joel]

You can't tell if the macros are causing the problem. It doesn't matter if
they are event driven or not event driven. If the are UDF functions then
they may make a difference. For example if you have the Date() function on
you workbook. Everytime the book is opened your workbook the sheets will be
recalculated and the UDF functions will get executed.

 

[Harry]

There are no UDF's. I found a couple of DATE functions, and converted them to
static dates. Still, no difference.

I have some volatile functions (OFFSET, ROW, COLUMN). However, the workbook
is in manual calculation mode. Could that be causing it to take as long as it
is?

 

[joel]

If it is only one workbook that you are having problems with then it is
usally because you have a lot of formulas on the worksheet. Earlier this
week somebody suggested the excel thinks some of your worksheet have the
wrong Lastcell making the sheet much larger than it actually is. What was
recommended was to go to each worksheet and type CNTL-END and check if the
last cell is beyong the end of your data. If it is then delete the rows and
columns between your data the the last cell. The workbook size should
decrease and you should get a speed improvement.

 

[Harry]

I ran FastExcel on the file and wasn't able to get much of a speed
improvement (i used the clean excess range option).
I also ran some of Debra Dalgesh's code to change the pivot table cache to
the same PT.

somehow, after saving those changes and moving the file to another PC, the
file opened right up.

Thanks for your help!

 

[joel]

I wonder if you just moved the file to another PC if that fixed the problem.
It may be an indication that the file is fragmented and you need to run a
deframenation on the original PC.