Change pivot cache data source

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,798
Office Version
365
Platform
Windows
Hi All

I have a workbook that contains several macros. One slices and dices data from another workbook. The next one will upload that table to an access database. Then another macro to pass parameters to a query in that access database.

The workbook then contains several pivot tables from that very same query table. Works a treat!

I would like to make it a little more bullet proof by adding in functionaility to change where the pivot cache collects its' data from. I still want it to read that database and that very same query, but I would like to be able to change the location of the access database just in case someone moves it to a different directory. So now the directory is C:\Workflow.mdb, but what happends if it moves to H:\Workflow.mdb.

The workbook only has the one pivot cache so I am wondering if I can change its source location in one hit without having to pass the entire SQL string through again.

Can anyone give me some suggestions please?
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,009
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
If you have multiple pivots based off one cache, it's very difficult to change that cache directly as you can if only one table is based off it. What you can do is create a new cache, then change the pivottables to point at that new cache. (You can assign the commandtext from one cache to the other)
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,798
Office Version
365
Platform
Windows
Rory

Can you give me an idea for the syntax to point an existing pivot table to a cache? I like this way cos I don't have to build all the pivot tables on the fly and should be able to refresh the whole lot.

Thanks!
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,009
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
You just set the CacheIndex property of the table to the relevant number. (you can use the Index property of the cache)
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,798
Office Version
365
Platform
Windows
Rory I'm stuck :(

I have what I think is correctly building a cache but when I test I still only count 1 cache and I imagine that it is the inital one. This bit (below) runs without error but I can't see what its index is and therefore don't know how to point my other pivot tables to it.

Also, what happens to the old cache? Can I kill it?

Code:
Option Explicit

Sub NewPivotCache()
 
    Dim recData As ADODB.Recordset
    Dim conDatabase As New ADODB.Connection
    Dim strAccessPath As String, strConnection As String, strSQL As String
    Dim pvcNew As PivotCache
    Dim PT As PivotTable
    
    strAccessPath = shtControl.Range("rngDbLocation").Text
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & strAccessPath & ";"
    
    conDatabase.Open strConnection
    
    strSQL = "SELECT PivotData_qry.* " & _
                "FROM PivotData_qry;"
                
    Set recData = New ADODB.Recordset
    Set recData.ActiveConnection = conDatabase
    recData.Open strSQL
    
    Set pvcNew = ThisWorkbook.PivotCaches.Add(xlExternal)
    Set pvcNew.Recordset = recData
    
    'shtTrend.PivotTables(1).CacheIndex = NEEDS TO REFERENCE THIS NEW CACHE
    
    recData.Close
    Set recData = Nothing
    Set pvcNew = Nothing
End Sub
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,009
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Hmm, yeah it seems you need to actually create a pivot table from the cache first. Try this:
Code:
Sub NewPivotCache()
 
    Dim recData As ADODB.Recordset
    Dim conDatabase As New ADODB.Connection
    Dim strAccessPath As String, strConnection As String, strSQL As String
    Dim pvcNew As PivotCache
    Dim PT As PivotTable
    Dim wks As Worksheet
    
    strAccessPath = shtControl.Range("rngDbLocation").Text
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & strAccessPath & ";"
    
    conDatabase.Open strConnection
    
    strSQL = "SELECT PivotData_qry.* " & _
                "FROM PivotData_qry;"
                
    Set recData = New ADODB.Recordset
    Set recData.ActiveConnection = conDatabase
    recData.Open strSQL
    
    Set pvcNew = ThisWorkbook.PivotCaches.Add(xlExternal)
    Set pvcNew.Recordset = recData
    Set wks = Sheets.Add
    pvcNew.CreatePivotTable wks.Range("A3")
    shtTrend.PivotTables(1).CacheIndex = pvcNew.Index
    Application.displayalerts = false
    wks.Delete
    Application.displayalerts = True
    recData.Close
    Set recData = Nothing
    Set pvcNew = Nothing
End Sub
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,798
Office Version
365
Platform
Windows
I think it is bizarre that we have to create the pivot table but I don't care because it works. :) Thanks Rory. And it seems that the old cache has been removed automatically so another bonus!
Thanks again (y)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,009
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
I think it is bizarre that we have to create the pivot table
Me too, though it may be that I'm just doing it wrong! :)
I think 2007 is easier for this, though I haven't actually tried it yet.
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,798
Office Version
365
Platform
Windows
Unfortunately this lot won't upgrade to 2007 and will go directly to 2010; and by that time I will be long gone. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,099,505
Messages
5,469,017
Members
406,628
Latest member
jared92

This Week's Hot Topics

Top