Excel VBA: Pivot Sourcedata Update

Question

I tried writing code to update the sourcedata source file that gave me this:

ActiveSheet.PivotTableWizard SourceType:=xlExternal, _ SourceData:=QueryArry1, _ Connection:=Array( _ Array("ODBC;DSN=MS Access Database;DBQ=" & DBDir & "\" & DBName & ";"), _ Array("DefaultDir=" & DBDir & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;") _ ) 

But this does not even allow me to specify the WHIH pivot table that I want to update ... or even do what I really want to do, which updates pivotcache to update all pivot tables using the same source.

So what is a good way to update sourcedata?

thanks

EDIT:

But I even get an error defined by the application or an object error, with something simple:

 str = Sheets("Totals").PivotTables("PivotTable2").PivotCache.CommandText Sheets("Totals").PivotTables("PivotTable2").PivotCache.CommandText = str 

And I double-checked that my pivot table still clicks on live data and updates it, but it still works ... but I can’t set the command line to what it is now? So strange.

thanks

Answer 1

PivotCaches are available through workbooks. You can list all of your current caches using the following subkey:

 Option Explicit Private Sub listCaches() Dim selectedCache As PivotCache For Each selectedCache In ThisWorkbook.PivotCaches Debug.Print selectedCache.Index Debug.Print selectedCache.Connection Next selectedCache End Sub 

You can access the connection you want to change with:

 ThisWorkbook.PivotCaches(yourIndex).Connection 

Note. After changing the connection, you should call:

 ThisWorkbook.PivotCaches(yourIndex).Refresh 

Edit: Instead of changing SourceData, you can change CommandText. This should have the same effect. The following code worked for me:

 ThisWorkbook.PivotCaches(1).CommandText = "SELECT movies.title, movies.rating, movies.comments FROM `C:\Folder\moviesDB`.movies movies" ThisWorkbook.PivotCaches(1).Refresh 

This code also updated my SourceData.

Edit2: Change CommandText command throgh PivotTable:

 Sheets("mySheet").PivotTables("PivotTable1").PivotCache.CommandText = "SELECT movies.title as meh, movies.rating, movies.comments FROM `C:\Folder\moviesDB`.movies movies" Sheets("mySheet").PivotTables("PivotTable1").PivotCache.Refresh 

Note: moviesDB is a .mdb file, and movies is a table / query

Debug.Print : This can also help you Debug.Print working CommandText before modifying it. This should give you a template for your new CommandText.