Mega Code Archive

 

Categories / VisualBasic Script / Excel

 

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