pivate tables
 

Hi;
Is there a way of accessing pivate tables from an excel spreadsheet in code?
Any help apreciated.
Sean.

pivate tables
 

Sean,

Here's some code I put in a worksheet module to try to learn the various VBA
properties of pivot tables. Maybe it will help you:

Sub PivotProperties()
Dim ws As Worksheet
Dim pvt As PivotTable
Dim pvtColumnField As PivotField
Dim pvtVisibleField As PivotField
Dim pvtItem As PivotItem
Dim pvtVisibleItem As PivotItem
Dim rngGrandTotalRow As Range
Dim rngGrandTotalCol As Range
Dim i As Long
Dim j As Long

Set ws = ActiveSheet
Set pvt = ws.PivotTables(1)
With pvt
For i = 1 To .VisibleFields.Count
Set pvtVisibleField = .VisibleFields(i)
With pvtVisibleField
Debug.Print vbCrLf & .Name & " Label range: " &
..LabelRange.Address & ":"
'all items, visible or hidden
For j = 1 To .PivotItems.Count
Set pvtItem = .PivotItems(j)
With pvtItem
Debug.Print .Name & " "
End With
Next j
'only visible items
For j = 1 To .VisibleItems.Count
Set pvtItem = .VisibleItems(j)
With pvtItem
Debug.Print .Name & " "; .DataRange.Address
End With
Next j
End With
Next i
Set rngGrandTotalRow = .DataBodyRange.Offset(.DataBodyRange.Rows.Count -
1, 0).Resize(1, .DataBodyRange.Columns.Count - 1)
Set rngGrandTotalCol = .DataBodyRange.Offset(0,
..DataBodyRange.Columns.Count - 1).Resize(.DataBodyRange.Rows.Count - 1, 1)
Debug.Print "Grand Total Column:" & rngGrandTotalCol.Address
Debug.Print "Grand Total Row:" & rngGrandTotalRow.Address
End With

End Sub

Doug

"Sean Farrow" wrote in message
...
Hi;
Is there a way of accessing pivate tables from an excel spreadsheet in
code?
Any help apreciated.
Sean.