Create a Pivottable based on data from Access

The example macro below shows how you can create a Pivottable in Excel based on data from an Access table or query. To run the example you will need an Access database with a table named "Customers" that contains the fields "CustomerID", "CustomerName", "City", "Region", "Country".

Note! Read and edit the source code before you use it!

Sub CreatePivotTableFromAccessData()
Const DataBaseName As String = "C:\FolderName\CustomerDB.mdb"
Const dbConnectionString As String = "ODBC;DBQ=" & _
    DataBaseName & ";Driver={Microsoft Access  Driver (*.mdb)};"
Const TableName As String = "Customers"
Dim varSource As Variant, pt As PivotTable
    Application.ScreenUpdating = False
    Workbooks.Add
    varSource = Array(dbConnectionString, "SELECT * FROM " & TableName)
    Set pt = ActiveSheet.PivotTableWizard(xlExternal, varSource, Range("A6"))
    With pt ' add information to the empty pivottable
       ' specify row field(s)
       With .PivotFields("City")
            .Orientation = xlRowField
            .Position = 1
        End With
       ' specify column field(s)
       With .PivotFields("Region")
          .Orientation = xlColumnField
          .Position = 1
       End With
       ' specify page field(s)
       .PivotFields("Country").Orientation = xlPageField
       ' specify data field(s)
       .PivotFields("CustomerID").Orientation = xlDataField
       ' set data region number format
       .DataBodyRange.NumberFormat = "#,##0"
    End With
    Set pt = Nothing
End Sub

Document last updated 2002-07-17 12:50:53 Printerfriendly version