VBA to change data source of pivot tables

[Lefemmenikita]

Hi

I have multiple excel workbooks with pivot tables pointing to an external spreadsheet (referred to as 'source')


Every month, I save both the excel workbooks and the source workbook into a new folder.



I don't rename any of the files.

is there any vba code which I can use to update the data source of the pivot tables in the target file so it points to the 'source' workbook which is saved in the same folder as it?



e.g. In the Sep-16 subfolder, if there is a workbook with a pivot, I want the pivot to point to the workbook called 'source' which is also saved in the Sep sub folder and not the file called 'source' saved in the Aug-16 subfolder.

Hope this makes sense.

I have attached screencaps of what this is supposed to look like

 

[sergioMabres]

Hi Nikita
You could use something like this

Sub setRefToCurrentPath()
    Dim myPath As String
    myPath = Application.ActiveWorkbook.Path
    ActiveSheet.PivotTables("YrPivotTable1").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        myPath & "\[source.xlsx]Sheet1!R1C1:R19C4", Version:=6)
    ActiveSheet.PivotTables("YrPivotTable1").PivotCache.Refresh
End Sub

Where
YrPivotTable1 is the name of your pivot table
Sheet1!R1C1:R19C4 is the range of your data in source.xlsx workbook

Do not use these names use your names in the macro

Cheers
Sergio

 

[Lefemmenikita]

Hi Sergio

Thanks for your help.


I tried this now and updated the sheet name references and the pivot table name ('pivottable1' to reflect the name per the actual table) and am getting the error:


'Run-time error 5:

Invalid procedure call or argument'


Thanks

 

[sergioMabres]

It looks you may have committed an error when replacing the values of pivot table name, sheet or range
Because my code was working in my test sheet with no error 5
Cheers
Sergio

 

[Lefemmenikita]

This is what I am using:

Sub setRefToCurrentPath()
    Dim myPath As String
    myPath = Application.ActiveWorkbook.Path
    ActiveSheet.PivotTables("[B]PivotTable1[/B]").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        myPath & "\[source.xlsx][B]Sheet1[/B]!R1C1:R19C4", Version:=6)
    ActiveSheet.PivotTables("[B]PivotTable1[/B]").PivotCache.Refresh
End Sub

I have checked the name of the pivot and the name of the sheet.

I'm not sure where I am going wrong?
thanks

 

[sergioMabres]

I think the error 5 is because you did not change the data range in source file, your macro uses the same range I used
"\[source.xlsx]Sheet1!R1C1:R19C4" you have to change to reflect the sheet name and range in RC format
Sergio

 

[Lefemmenikita]

The file was called 'source.xlsx' and the sheet was 'sheet1'

The row and column references (19 rows, 4 columns) also fit so it's most likely not that I think

 

[sergioMabres]

I am sorry Nikita but I cannot do remote support without the actual files
Cheers
Sergio