Populate pivottables using ADO

  • In this example I will show how we can populate a created pivottables with data from an Access-database and refresh the pivottable.


    The example does not go in detail about a pivottable's properties. The best way to find out and do get a start is to use the macro-recorder and then clean it up.


    What we need:
    * MS Windows 2000 and later
    * MS Excel 2000 and later
    * MDAC 2.5 or above (ADO Library)


    Step 1
    Add a reference to MS ADO Library x.x via Tools | Reference... in the VB-Editor.


    Step 2
    Insert following code in a standardmodule:


    The property connection of the Pivotcache (and Pivottable) accept only ODBC-connections and therefore the Pivotcache must be populated with a recordset when using the ADO-approach.


    For an example of populating pivottables with ODBC-connections please see:
    http://www.excelkb.com/article.aspx?id=10131