VBA - change PivotTable source for every PivotTable within worksheet.

[d'Holbach]

I have a rather complex report which is based on data it references from specifically set out and formatted 9 PivotTables located on a seperate worksheet. The PivotTables pull data from an Excel spreadsheet (specific range) which is located on a network drive. At the moment whenever I want to run the report with new version of data I'm manually updating data sources of all PivotTables to refresh the report by selecing PivotTable and going Change Data Source and selecting a range from external worksheet and repeating this action for all PivotTables.

The connection string I use in the Change Data Source looks like this:
"K:\FolderA\FolderB\FolderC\2013-14\\[nameOfTheFile.xlsx]Data 13-14'!$A$1:$GM$130253"

This is a pseudocode of what I would like to achieve:

For each PivotTable in Worksheet
                PivotTable.DataSource = "new source"
                PivotTable.Refresh
         Next PivotTable

 

[d'Holbach]

I'm having problems with updating the SourceData. Even executing this code fails with 1004 error on the bold line:

Sub UpdateSourceDataString()

Dim newSourceString As String
newSourceString = ActiveWorkbook.PivotCaches(1).SourceData

[B]ActiveWorkbook.PivotCaches(1).SourceData = newSourceString
[/B]ActiveWorkbook.PivotCaches(1).Refresh

End Sub

Can someone please advise what I'm doing wrong and how can I update the SourceData property?

 

[Andrew Poulsom]

To do that the source must be open. The SourceData property returns a string like:

[PivotSource.xlsm]Sheet1!R1C1:R4C2

 

[d'Holbach]

Unfortunately even with the source workbook open it fails with 1004 error on that same line.

Sub UpdateSourceDataString()
Dim newSourceString As Variant

newSourceString = "'[PivotSource.xlsx]Sheet1'!R1C1"

ActiveWorkbook.PivotCaches(1).SourceData = newSourceString

ActiveWorkbook.PivotCaches(1).Refresh

End Sub

 

[Andrew Poulsom]

Maybe that's because you can't create a pivot table from a single cell. What does:

MsgBox ActiveWorkbook.PivotCaches(1).SourceData

return?

 

[d'Holbach]

Sorry, in my previous example I've replaced the correct file path, file name and range with a dummy one and with a dummy range.

This is what is being returned (after folder names and file names have been changed) and what I've been using:

'K:\Folder1\Folder2\Folder3\2013-14\5. Aug 1314\[NameOfTheFile.xlsx]Data 13-14'!R1C1:R53497C195

 

[Andrew Poulsom]

Do you still have a question?

 

[d'Holbach]

Yes, the sub routine which is supposed to update the sourcedata property to the new source is not working, it fails on this line:

ActiveWorkbook.PivotCaches(1).SourceData = newSourceString

with error 1004

My question is how I can make it to work.

 

[Andrew Poulsom]

Does it work if you open the new source workbook and use:

'[NameOfTheFile.xlsx]Data 13-14'!R1C1:R53497C195

for newSourceString.

 

[d'Holbach]

No, unfortunately it does not.

I've tried both options: only filename & range and full path and with various data sources (spreadsheets) both opened and closed when running the code.

 

[Andrew Poulsom]

This worked for me with PivotSourceNew.xlsm open:

Sub Test()
    Dim newSourceString As String
    newSourceString = "'[PivotSourceNew.xlsm]Sheet1'!R1C1:R4C2"
    ThisWorkbook.PivotCaches(1).SourceData = newSourceString
End Sub