Problem opening excel file with Pivot Tables

[owentmoore]

Hi All,

I had an excel file with 25000 rows of data and 26 columns. I had approx 10 pivot tables created off this data. The data and pivot tables are in the same file. The file size was 40MB.

In order to reduce the file size I went through all my pivot tables individually and put the source data for them the same as my first pivot table. This reduced the file size to 9MB.

Instead of improving the time to open, it has got worse - much worse. I thought by consolidating my pivot sources that this would reduce the file size (successful) and reduce time to open (un-successful)

Anyone have any ideas of what wnet wrong and how I can fix it?

Owen

 

[combo]

Depending on your needs and data source (internal vs. external), you can try to change pivot table options:
- do not keep data with pivot table,
- if set so, untick refreshing table on open.

combo

 

[xlbo]

The way you have it set up now (as far as you have explained) is the best way to have it. Other than what combo suggests, there is not too much you can do - maybe try detaching the pivot tables from the source data so you are querying a different workbook...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[owentmoore]

Thanks guys.

I've unticked the refresh on open option which has applied to all my pivot tables, deleted unwanted information, removed unused macros, bipassed the on_open event. Re-linked any macros to their buttons. I found a few links that were incorrect, wrong references etc - all fixed.

I've re-opened the file and it has opened in a matter of a couple of seconds.

I've then re-enabled the on_open event - again it opens with no problems.

I've then re-ticked the refresh on open box and tried to open again - success - it opens quickly.

The issue must have been with the broken links or something.

Thanks

Owen

 

[xlbo]

yup - broken links will kill open times because excel will keep trying to make the link for x amount of time (where x is far too long IMO ;-) )

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question