Excel VBA: Update Pivot Souredata

Question

I tried to record the code to update a pivot sourcedata which 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 doesn't even allow me to specify WHICH pivot table I want to update... or even do what I really want to do which is update the pivotcache so that all pivot tables using that same source are updated.

So what is a good way to update the sourcedata?

Thanks

EDIT:

But I even get the "application-defined or object-defined error" error with something as simple as:

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

And I did double check that my pivot table is still hitting the live data and refreshing it still works... yet I can't set the command string to what it is currently? So bizarre.

Thanks

Answer 1

The PivotCaches are accessible through Workbooks. You can list all your current caches with the following sub:

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 edit with:

ThisWorkbook.PivotCaches(yourIndex).Connection

Note: After changing the Connection you should call:

ThisWorkbook.PivotCaches(yourIndex).Refresh

Edit: Instead of changing the SourceData you can change the CommandText. That 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: Changing CommandText 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 the table/query

Note2: It might also help you to Debug.Print the working CommandText before changing it. This should give you a template for your new CommandText.