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: