VBA/Excel/Access/Word/Excel/PivotTable
Содержание
- 1 add calculated items to a field using the Add method of the CalculatedItems collection
- 2 Add data field to PivotTable
- 3 adds a PivotTable based on the data from an Access database
- 4 Assign a value to the Orientation property of the PivotField object, as shown here:
- 5 Create Pivot Table From database
- 6 Creating a PivotTable Report
- 7 Modifying Pivot Tables
- 8 PivotTables Collection
- 9 Redefines the layout of the fields in the existing Table, apart from the data field
- 10 remove the CalculatedItem by deleting it from either the CalculatedItems collection or the PivotItems collection of the PivotField:
- 11 Visible Property
add calculated items to a field using the Add method of the CalculatedItems collection
<source lang="vb"> Sub AddCalculatedItem() With ActiveSheet.PivotTables(1).PivotFields("Product") .CalculatedItems.Add Name:="Melons", Formula:="=Mangoes*1.5" End With End Sub </source>
Add data field to PivotTable
<source lang="vb"> Sub AddDataField() Dim pvt As PivotTable Set pvt = ActiveSheet.PivotTables(1) With pvt.PivotFields("Revenue") .Orientation = xlDataField .NumberFormat = "0" End With With pvt.DataFields("Sum of NumberSold") .Position = 2 .Function = xlCount .NumberFormat = "0" End With End Sub </source>
adds a PivotTable based on the data from an Access database
<source lang="vb"> 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 </source>
Assign a value to the Orientation property of the PivotField object, as shown here:
<source lang="vb"> Sub AddFieldsToTable() With ActiveSheet.PivotTables(1) .AddFields RowFields:="State", AddToTable:=True .PivotFields("Date").Orientation = xlPageField End With End Sub </source>
Create Pivot Table From database
<source lang="vb">
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 .rumandText = 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
</source>
Creating a PivotTable Report
<source lang="vb"> Sub CreatePivotTable() Dim myWorksheet As Worksheet Dim pvc As PivotCache Dim pvt As PivotTable Set myWorksheet = Worksheets.add Set pvc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Sheet1.ListObjects("Table1").range) Set pvt = pvc.CreatePivotTable(TableDestination:=myWorksheet.range("A3"), _ DefaultVersion:=xlPivotTableVersion12) With pvt With .PivotFields("Customer") .Orientation = xlRowField .Position = 1 End With With .PivotFields("Product") .Orientation = xlColumnField .Position = 1 End With .AddDataField .PivotFields("NumberSold"), "Sum of NumberSold", xlSum End With End Sub </source>
Modifying Pivot Tables
<source lang="vb">
Private Sub OptionButton1_Click()
Application.ScreenUpdating = False With ActiveSheet.PivotTables(1).PivotFields("Month") .PivotItems("Jan").Visible = True .PivotItems("Q4").Visible = False End With
End Sub
</source>
PivotTables Collection
<source lang="vb"> Sub AddTable() Dim pvc As PivotCache Dim pvt As PivotTable Set pvc = ActiveWorkbook.PivotCaches(1) Set pvt = ActiveSheet.PivotTables.Add(PivotCache:=pvc, TableDestination:=Range("A3")) End Sub </source>
Redefines the layout of the fields in the existing Table, apart from the data field
<source lang="vb"> Sub RedefinePivotTable() Dim pvt As PivotTable Set pvt = ActiveSheet.PivotTables(1) pvt.AddFields RowFields:=Array("Product", "Customer"), _ ColumnFields:="State", _ PageFields:="Date" End Sub </source>
remove the CalculatedItem by deleting it from either the CalculatedItems collection or the PivotItems collection of the PivotField:
<source lang="vb">
Sub DeleteCalculatedItem()
With ActiveSheet.PivotTables(1).PivotFields("Product") .PivotItems("Melons").Delete End With
End Sub
</source>
Visible Property
<source lang="vb"> Sub CompareMonths() Dim pvt As PivotTable Dim pvi As PivotItem Dim sMonth As String sMonth = "Jan" Set pvt = ActiveSheet.PivotTables(1) For Each pvi In pvt.PivotFields("Years").PivotItems If pvi.Name <> "2006" And pvi.Name <> "2007" Then pvi.Visible = False End If Next pvi pvt.PivotFields("Date").PivotItems(sMonth).Visible = True For Each pvi In pvt.PivotFields("Date").PivotItems If pvi.Name <> sMonth Then pvi.Visible = False Next pvi End Sub </source>