PDA

View Full Version : Overview values pivot table



Philips
08-08-2018, 05:29 AM
Hi all,

I'm trying to make an overview of all pivot values used in my current worksheet.
I tried to search on the "xlDataField" and getting all information, like wbName, wsName, ValueName, ... but I keep getting a blank overview...

any suggestions??


Thanks in advance!

p45cal
08-08-2018, 06:14 AM
I'm trying to make an overview of all pivot values used in my current worksheetI'm not sure what pivot values are but this will run through all pivot tables on the active sheet:
For Each pt In ActiveSheet.PivotTables
Debug.Print pt.Name, pt.Parent.Name, pt.Parent.Parent.Name, pt.TableRange2.Address, pt.DataBodyRange.Address
Next
and provide a bit of information on each in the Immediate pane of the VBEditor

Philips
08-08-2018, 06:35 AM
Hi, thanks for the fast reaction!
I'm trying to get an overview off all fields used in the pivots in my workbook.
In my code, I use a case to get all fields from xlPageField, xlRowField, xlColumnField; which results in:
22683

If I integrate the xlDataField parameter, my code crashes :doh:

p45cal
08-08-2018, 08:11 AM
Show the code here, it'll probably just need a tweak.

Paul_Hossler
08-08-2018, 08:22 AM
I think .DataFields fails if there aren't any.

You could use .DataFields.Count > 0 then ….

But try this as a start




Option Explicit
Sub ListPT()
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim o As Long


o = 2

For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
For Each pf In pt.PivotFields
Worksheets("Summary").Cells(o, 1).Value = ws.Name
Worksheets("Summary").Cells(o, 2).Value = pt.Name
Worksheets("Summary").Cells(o, 3).Value = pf.Name
Select Case pf.Orientation
Case xlColumnField
Worksheets("Summary").Cells(o, 4).Value = "Column"
Case xlDataField
Worksheets("Summary").Cells(o, 4).Value = "Data"
Case xlHidden
Worksheets("Summary").Cells(o, 4).Value = "Hidden"
Case xlPageField
Worksheets("Summary").Cells(o, 4).Value = "Page"
Case xlRowField
Worksheets("Summary").Cells(o, 4).Value = "Row"
End Select

o = o + 1
Next
Next
Next



End Sub

Philips
08-08-2018, 11:23 PM
Hi,

this is the code I use at this moment:

For Each ws In wb.Worksheets
If ws.PivotTables.Count > 0 Then
For Each pt In ws.PivotTables


For Each pf In pt.PivotFields
Select Case pf.Orientation
Case xlPageField: strLoc = "Filter"
Case xlRowField: strLoc = "Row"
Case xlColumnField: strLoc = "Column"
Case xlDataField: strLoc = "Value"
Case Else: strLoc = "" 'only list row, column, filter
End Select

If strLoc <> "" Then
strPF = pf.Name
strPI = PFItems & " Items"
Select Case bAll
Case True: strVis = "Y"
Case Else: strVis = "N/A"
End Select

With wsList
.Range(.Cells(lRow, 1), _
.Cells(lRow, lCols)).Value = Array(wb.Name, ws.Name, strLoc, strPF)
End With
lRow = lRow + 1
End If
Next pf
Next pt
End If


Debug.Print ws.Name
Next ws

p45cal
08-09-2018, 02:26 AM
I'm out of time today so in haste (I will come back to it tomorrow):
In my experience (not comprehensive) I've never seen a pivot field's .Orientation property return xlDataField (enumerates to 4); even if you write code to make it so, and as a result you see the field in the Values/Data area of the pivot table, when you look at the property (and when you query it) it always returns xlHidden.
I think you may be better served if you use a For Each loop for each of .RowFields, .PageFields, .ColumnFields and .DataFields:
(Air code because I'm in a hurry)

For each pf in pivottable.rowfields
'get your names here (they're all in the rows section)
next pf
For each pf in pivottable.columnfields
'get your names here (they're all in the column section)
next pf
For each pf in pivottable.pagefields
'get your names here (they're all in the filter/page section)
next pf
For each pf in pivottable.datafields
'get your names here (they're all in the values/data area)
next pf

If there are no pivotfields in any particular section the code shouldn't falter.
You'll get your pivot fields grouped and in any order you want (rows,columns etc.)

Paul_Hossler
08-09-2018, 04:44 AM
P45cal was right about .Orientation not working as expected

This might be a little more better

Since you can rename field names in the PT and data values look like "Sum of …." or "Average of …", .SourceName might be better





Option Explicit
Dim o As Long
Sub ListPT()
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField


o = 2

For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
For Each pf In pt.PageFields
Call AddToSummary(pf, "Page")
Next
For Each pf In pt.RowFields
Call AddToSummary(pf, "Row")
Next
For Each pf In pt.ColumnFields
Call AddToSummary(pf, "Column")
Next
For Each pf In pt.DataFields
Call AddToSummary(pf, "Data")
Next
Next
Next
End Sub



Private Sub AddToSummary(F As PivotField, s As String)
Dim v As Variant
Dim c As String
Dim i As Long

With Worksheets("Summary")
.Cells(o, 1).Value = F.Parent.Parent.Parent.Name
.Cells(o, 2).Value = F.Parent.Parent.Name
.Cells(o, 3).Value = F.Parent.Name
.Cells(o, 4).Value = F.SourceName
.Cells(o, 5).Value = s
End With
o = o + 1
End Sub

Philips
08-09-2018, 05:05 AM
thanks! It is working now :clap::clap::clap::clap::clap::clap: