PivotCache Question

  • Thread starter Thread starter John Michl
  • Start date Start date
J

John Michl

I'm new to using pivot tables in VBA code. I have a few questions.

I have a sheet that has six pivot tables. The first one was created
manually using the wizard. Three others were created by copying the first.
The other two were created using the wizard and selecting "Pivot table" as
the data source.

Question 1: Do all of these tables use the same PivotCache and if so, if I
Refresh the Cache for the first table, is the cache updated for the others?
(The raw data is in a SQL database.)

Question 2: If they use different Caches, is there a way to designate that
they should use the same cache so that my file size is smaller and doesn't
include redundant data?


I have several other questions but I'll start with these two first.

Thanks.
 
I may have answered my own questions but now I have another.

It appears that there is only one pivot cache for these tables. I suspect
this because I executed the following:

Sub RefreshPivots
Thisworkbook.refreshtables
End Sub

I only saw it cycle through the records once (the cache, I assume) before
the pivot tables were all updated. Here's the new question.

This worksheet links to an SQL database with a set structure. I'd like to
use this worksheet on many other databases. They'd all have exactly the
same structure but the name and perhaps location would be different. Can I
somehow change the location of the database the cache refers to without
recreating all of the pivots? I did something similar in Access. All I
needed to do is go to the linked table manager and change the source
database. Any ideas? Thanks.
 
Back
Top