Update Multiple Pivot Tables with VBA

[Gino_Vernisci]

Hey Guys

I have a report that I put together monthly. The report contains 38 different pivot tables all of which reference a single separate excel workbook.

I would like to write a VBA macro that changes the data source for all 38 Pivot Tables to a new excel workbook and updates each pivot table. I do not need to reformat any of the pivot tables. Simply changing the data source and updating the pivot tables is sufficient.

Ideally the code could update all of the Pivot Tables in the report at once without instructing the computer to go to each individual sheet and carry out the procedure however I am not sure this is possible.

I have found the function "expression.changeconnection(conn)" which may be the place to start. I would appreciate any help you could give.

If you need any further information please say so and I will respond promptly.

Cheers
Gino

Windows 7 Excel 2010

 

[Firefly2012]

Hi Gino

How many pivotcaches (not pivot tables) does this workbook contain? If all your pivot tables reference the same table of data in the source workbook, you may well have only a single pivotcache and will only need to amend the connection of this.

With your workbook containing the pivottables open in Excel, open up the VBE (Alt+F11), open up the Immediate Window (Ctrl+G) and type in the following (exactly as it appears):

?ActiveWorkbook.PivotCaches.Count

and report back what number is returned

 

[Gino_Vernisci]

The steps you provided returned "1"

 

[Firefly2012]

Good. Next thing to do is to return to the Immediate Window and type in the following:

?ActiveWorkbook.Pivotcaches(1).Connection

and post back what this returns

 

[Gino_Vernisci]

Entering this command in the immediate window produces a run time error

Run-time error '1004':
Application-defined or object-defined error

Is this expected or have I made a mistake?

 

[Firefly2012]

No it was me that made the mistake - could you rty typing this in instead:

?activeworkbook.PivotCaches(1).SourceData

 

[Gino_Vernisci]

FireFly

The command produced this:

'[TTM Data 12.7.11.xlsx]Data'!R1C1:R202246C53

 

[Firefly2012]

Excellent! I think all we need to know now is the name & path of the new file you will want to change this to, the sheet name on which the new data will reside and the range of data on this sheet.

What we can do then is craft a macro which will change the SourceData property you accessed above to the new file.

Potentially, what we could do is have the macro ask you to select the new file (eg thru a dialog) and then specify the cells from the new file that you want to be the new data source for the pivot tables.

How does that sound?

i will start working on such a solution now (may take me a couple of hours given my need to eat/test etc but I should have something for you by late tonight)

 

[Firefly2012]

It would be really useful to know if there is a standard location where the new data file will be stored (eg a folder into which it will always be saved down).

 

[Gino_Vernisci]

Firefly

That is basically exactly what I had in mind. While talking with you I have also been learning about userforms which would prompt the user to enter the workbook, sheet and data range for the new reference workbook.

I appreciate your help enormously. I would love to see your code whenever you complete it however if it would save you time I would be completely satisfied to see just the syntax for the function that changes the reference workbook.

Thanks again
Gino