pivate tables

  • Thread starter Thread starter Sean Farrow
  • Start date Start date
S

Sean Farrow

Hi;
Is there a way of accessing pivate tables from an excel spreadsheet in code?
Any help apreciated.
Sean.
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top