Create Pivot Table From database : PivotTable « Excel « VBA / Excel / Access / Word

Create Pivot Table From database

 

Sub CreatePivotTableFromDB()
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    
    Set PTCache = ActiveWorkbook.PivotCaches.Add (SourceType:=xlExternal)
    
    DBFile = ThisWorkbook.Path & "\budget.mdb"
    ConString = "ODBC;DSN=MS Access Database;DBQ=" & DBFile
    QueryString = "SELECT * FROM '" & ThisWorkbook.Path & "\BUDGET'.Budget Budget"
    
    With PTCache
        .Connection = ConString
        .CommandText = QueryString
    End With
    
    Worksheets.Add
    ActiveSheet.Name = "PivotSheet"

    Set PT = PTCache.CreatePivotTable(TableDestination:=Sheets("PivotSheet").Range("A1"), TableName:="BudgetPivot")
    
    With PT
        .PivotFields("DEPARTMENT").Orientation = xlRowField
        .PivotFields("MONTH").Orientation = xlColumnField
        .PivotFields("DIVISION").Orientation = xlPageField
        .PivotFields("ACTUAL").Orientation = xlDataField
    End With
End Sub

Related examples in the same category

1.	Modifying Pivot Tables
2.	Creating a PivotTable Report
3.	PivotTables Collection
4.	Assign a value to the Orientation property of the PivotField object, as shown here:
5.	Redefines the layout of the fields in the existing Table, apart from the data field
6.	Add data field to PivotTable
7.	Visible Property
8.	add calculated items to a field using the Add method of the CalculatedItems collection
9.	remove the CalculatedItem by deleting it from either the CalculatedItems collection or the PivotItems collection of the PivotField:
10.	adds a PivotTable based on the data from an Access database