Add Excel 2003 Pivot Cache Connected to MS Access

I have an Excel 2003 workbook with 6 sheets and a pivot cache for each sheet. Each pivot cache SourecTYpe=1, i.e., the SourceData is an Excel range in the workbook.  Using Excel VBA, I want to change the source of the data for all six pivot tables to an MS Access 2003 table. I believe I first need to create a new pivot cache that has SourceType=2 and strings for Connection and CommandText. I need sample code for:
-creating a new pivot cache that has its source data in MS Access
-change the source data for the existing pivot tables to the new MS Access pivot cache
Also, do I delete the old Excel caches?
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

Rory ArchibaldConnect With a Mentor Commented:
Here's a rough bit of code. Note that you do not need to delete the old pivotcaches (nor can you) - Excel will remove any that are no longer in use.

Dim PC As PivotCache, PT As PivotTable
    Set PC = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
    With PC
        .Connection = _
        "ODBC;DSN=MS Access Database;DBQ=C:\test1.mdb;DefaultDir=C:;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
        .CommandType = xlCmdSql
        .CommandText = "SELECT Table1.Date,, Table1.Type, Table1.value FROM `C:\test1`.Table1 Table1"
        ' need to create a table to use the new cache
        Set PT = .CreatePivotTable(TableDestination:="Sheet2!R3C1", TableName:="", _
    End With
    ' assign new cache to existing pivot table
    Sheets("Sheet5").PivotTables(1).CacheIndex = PC.Index

Open in new window

paulmcneilAuthor Commented:
Thanks rorya. This is exactly what I needed!
All Courses

From novice to tech pro — start learning today.