adds a PivotTable based on the data from an Access database

 
     Sub PivotTableDataViaADO()
         Dim con As ADODB.Connection
         Dim rs As ADODB.Recordset
         Dim sSQL As String
         Dim pvc As PivotCache
         Dim pvt As PivotTable

         Set con = New ADODB.Connection
         con.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & CurrentProject.Path & "SalesDB.accdb;"
         sSQL = "Select * From SalesData"
         Set rs = New ADODB.Recordset
         Set rs.ActiveConnection = con
         rs.Open sSQL

         Set pvc = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
         Set pvc.Recordset = rs

         Worksheets.Add Before:=Sheets(1)
         Set pvt = ActiveSheet.PivotTables.Add(PivotCache:=pvc, _
                 TableDestination:=Range("A1"))

         With pvt
             .NullString = "0"
             .SmallGrid = False
             .AddFields RowFields:="State", ColumnFields:="Product"
             .PivotFields("NumberSold").Orientation = xlDataField
         End With
     End Sub

Related examples in the same category

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

adds a PivotTable based on the data from an Access database : PivotTable « Excel « VBA / Excel / Access / Word

Related examples in the same category