VBA/Excel/Access/Word/Excel/PivotTable — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Текущая версия на 12:47, 26 мая 2010
Содержание
- 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
Sub AddCalculatedItem()
With ActiveSheet.PivotTables(1).PivotFields("Product")
.CalculatedItems.Add Name:="Melons", Formula:="=Mangoes*1.5"
End With
End Sub
Add data field to PivotTable
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
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
Assign a value to the Orientation property of the PivotField object, as shown here:
Sub AddFieldsToTable()
With ActiveSheet.PivotTables(1)
.AddFields RowFields:="State", AddToTable:=True
.PivotFields("Date").Orientation = xlPageField
End With
End Sub
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
.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
Creating a PivotTable Report
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
Modifying Pivot Tables
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
PivotTables Collection
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
Redefines the layout of the fields in the existing Table, apart from the data field
Sub RedefinePivotTable()
Dim pvt As PivotTable
Set pvt = ActiveSheet.PivotTables(1)
pvt.AddFields RowFields:=Array("Product", "Customer"), _
ColumnFields:="State", _
PageFields:="Date"
End Sub
remove the CalculatedItem by deleting it from either the CalculatedItems collection or the PivotItems collection of the PivotField:
Sub DeleteCalculatedItem()
With ActiveSheet.PivotTables(1).PivotFields("Product")
.PivotItems("Melons").Delete
End With
End Sub
Visible Property
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