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

 

[Gino_Vernisci]

In answer to your question:

I had imagined simply putting the data file on the desktop of my computer until the report update is completed.

 

[Firefly2012]

Gino, try the following.

1. Place it in the same workbook as that which contains the pivot tables.
2. With the pivot table workbook open, open up the VBE and insert a standard module into the project (Insert>Module)
3. Paste all the code below into this module and save the workbook.
4. Try the code by running the macro named "Change_Pivot_Cache_Data_Source"

Sub Change_Pivot_Cache_Data_Source()
Dim strFile As String
Dim wb As Workbook
Dim rng As Range
Dim strNewDataSource As String
Dim xlCalc As XlCalculation
With Application
    xlCalc = .Calculation
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
End With

With Application.FileDialog(msoFileDialogFilePicker)
    .Title = "Please select the file containing the new data range"
    .AllowMultiSelect = False
    .InitialFileName = SpecialFolderPath()
    If .Show <> -1 Then MsgBox "No file selected! Exiting Sub!": Exit Sub
    strFile = .SelectedItems(1)
End With

Set wb = Workbooks.Open(Filename:=strFile, UpdateLinks:=False, ReadOnly:=True)
wb.Sheets(1).Activate
'On Error GoTo err_handler
Set rng = Application.InputBox("Please select the entire data range (including headers) " & _
            "that you want to be used by the pivot tables", "Select Data Range", Type:=8)
strNewDataSource = "'" & wb.Path & "\[" & wb.Name & "]" & rng.Parent.Name & "'!" & rng.Address(1, 1, xlR1C1)
Debug.Print strNewDataSource
ThisWorkbook.PivotCaches(1).SourceData = strNewDataSource
wb.Close savechanges:=False
Application.Calculation = xlCalc
End Sub
Function SpecialFolderPath() As String
     
    Dim objWSHShell As Object
    Dim strSpecialFolderPath
     
     'On Error GoTo ErrorHandler
     ' Create a shell object
    Set objWSHShell = CreateObject("WScript.Shell")
     ' Find out the path to the passed special folder,
     '  just change the "Desktop" for one of the other options
    SpecialFolderPath = objWSHShell.SpecialFolders("Desktop")
     ' Clean up
    Set objWSHShell = Nothing
    Exit Function
ErrorHandler:
     
    MsgBox "Error finding " & strSpecialFolder, vbCritical + vbOKOnly, "Error"
End Function

 

[Gino_Vernisci]

Firefly

I followed your instructions on entering the code into the module of the pivot table workbook. When I run the code I am able to select the new reference file, the file opens and prompts me for the range of data. When I select the range and hit "ok" I get

run time error '1004'.

Debugging flags the line:

ThisWorkbook.PivotCaches(1).SourceData = strNewDataSource

three lines from the bottom of the first sub.

 

[Firefly2012]

Gino

You should find that a string has printed out to the Immediate Window when the code is run - can you post up what this string is (it's the path to your workbook and the range being updated to the pivot table)?

Apart from possibly the number of rows increasing/decreasing, you aren't changing the number of columns and/or the headers (names) of the columns when you are trying to change the source?

Incidentally, I did a very limited test of the code on a mega-simple pivot table using a couple of different excel workbooks as the external sources and the code worked for me so I know in principal it does work. We just need to establish why it might not be working for you.

 

[boris1]

[h=1][/h]Hello,
I've read through the existing help on this topic but am too rusty to get it working technically. I sort of need my hand held because I don't recall any VB syntax.
My pivot table source data is in sheet "raw data" my pivot tables are many across many worksheets. Can I have a script work per sheet or per workbook?
The update to the raw data is only the large range on sheet "raw data"