Change Pivot Table data source through VBA

Question

Hi,

Is it possible to change data sources of all pivot tables created through wizard in different sheets of workbook through VBA. Data is coming from other excel sheet, user might change the location or name of source file. I want to give them option to save the path and file name in two different cells. When required run a sub which update old path\file name with new new path\file name.

regards,

Answer 1

Hi,

assuming that source data is in Workbook1.xlsx, in Sheet1, in range A1:D100

and workbook1 path is in workbook2, in Sheet3, in cell A1

step1

Save As Workbook2 with extension .xlsm (macros enabled)

step2

in a regular module (in workbook2.xlsm) paste in the following macro:

Sub PT_ChangeSourceData()

For i = 1 To 2

Sheets(i).Activate

ActiveSheet.PivotTables(1).ChangePivotCache _

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

**Sheets("Sheet3").Range("A1").Value & "[Workbook1.xlsx]Sheet1!$A$1:$D$100",**Version:=xlPivotTableVersion14)

Next

End Sub

xxxxxxxxxxxxx

or

if your data source table is dynamic

try this code...

Sub Dynamic_ChangeSourceData()

'Mar 09, 2015

Application.ScreenUpdating = False

Set wb = Workbooks.Open(Sheets("Sheet3").Range("A1").Value & "Workbook1.xlsx")

Dim rng

rng = wb.Sheets(1).Range("A1").CurrentRegion.Address

wb.Close False

For i = 1 To 2

Sheets(i).Activate

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

Sheets("Sheet3").Range("A1").Value & "[Workbook1.xlsx]Sheet1!" & rng, Version:=xlPivotTableVersion14)

Next

ActiveWorkbook.Save

Application.ScreenUpdating = True

End Sub

note

path in Sheet3, in cell A1 is like

*c:\folder1\folder2*

Answer 2

Thank you for your interest to help me.

for example:

Data source is in Workbook1, sheet1.

Pivot tables are in Workbook2, sheet1, sheet2

Pivot tables are created through wizard. If location of Workbook1 is changed. Instead going in to properties of PivotTable and change the data source. I want VBA code to read new path from Workbook2, sheet3, cell A1 and change the data source of pivot tables in sheet1 and sheet2.

Regards.

Answer 3

atensari,

Since your asking for code I assume you have at least basic skills in writing VBA. 

Turn on the macro recorder in the developer tab and manually change the source for one of your pivot tables. Turn off the macro recorder in the developer tab and look at the recorded code

this will give you the key code you need.  You will then modify that code to pick up the new source from you designated cell.  

then you can add code like this

Sub abc()

Dim sh as worksheet

Dim pt as PivotTable

for each sh in worksheets

  for each pt in sh.pivottables

     'Your code modified to refer to 'pt'

  Next

Next

End sub

--

Regards,

Tom Ogilvy

Answer 4

Hello,

Since your query is related to Pivot tables, you may also post this in TechNet forum for better suggestions.

http://social.technet.microsoft.com/Forums/en/excel/threads

If you have any other question, feel free to ask.

Thank you.