Add Excel 2003 Pivot Cache Connected to MS Access

Posted on 2010-01-04
Last Modified: 2012-05-08
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?
Question by:paulmcneil
    LVL 85

    Accepted Solution

    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


    Author Comment

    Thanks rorya. This is exactly what I needed!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now