в 16:33, 26 мая 2010

2010-05-26T16:33:00Z

Admin: 1 версия

2010-05-26T12:47:39Z

1 версия

Новая страница

==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>

← Предыдущая	Версия 16:33, 26 мая 2010
(нет различий)

VBA/Excel/Access/Word/Excel/PivotTable - История изменений

в 16:33, 26 мая 2010

Admin: 1 версия