MyTetra Share
Делитесь знаниями!
adds a PivotTable based on the data from an Access database
20.01.2018
14:39
Текстовые метки: pt,pivot,create pt,создание сводной
Раздел: VBA - Сводные

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

 
Так же в этом разделе:
 
MyTetra Share v.0.52
Яндекс индекс цитирования