Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get visible pivot table items
Hi all,
I have a dedicated "data selection" pivot table on one of my sheets. The user is free to move the fields into the rows, columns and report filter. After the user has set up the fields the way he likes, and after he has applied all filters he needs, I need to capture the total filtering on the fields in order to apply it to the other pivot tables in the same workbook. I want to do this by looking which items are / aren't displayed in this "data selection" pivot. And here comes the question: I already found out that I cannot simply use PivotField.HiddenItems, since that won't work for page fields. I therefore just loop through all fields and items and inspect their PivotItem.Visible property, but now I find out this doesn't work for label filters - it only returns if the item is manually selected (ticked) / deselected in the field item list. What options do I have? I know I can use PivotTable.ActiveFilters to look what label filters have been applied, but I cannot seem to get how they have been processed on the item list itself. -- Kind regards, Carl Colijn TwoLogs - IT Services and Product Development A natural choice! http://www.twologs.com TimeTraces: the powerful and versatile time registration system! http://timetraces.twologs.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get visible pivot table items
I have written an add-in for myself that allows to save a pivottable
layout with all setttings and then I can restore this saved layout when I want to. Because there are problems to read certain filterings, as you discovered,, I'm temporarly changing the field orientation, then reading the filtering, and then restoring the field to its original orientation. I still have a one problem with the grouping of data, e.g. when dates are grouped by months, quarters,.. I found no way that I can read the current grouping settings. Looks like MS forgot to implement it. I can set with VBA or VB.net the grouping but i didn't find a straightfwd way to read the grouping settings. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get visible pivot table items
On Tue, 5 Apr 2011 00:53:27 -0700 (PDT), minimaster
wrote: I have written an add-in for myself that allows to save a pivottable layout with all setttings and then I can restore this saved layout when I want to. Because there are problems to read certain filterings, as you discovered,, I'm temporarly changing the field orientation, then reading the filtering, and then restoring the field to its original orientation. I still have a one problem with the grouping of data, e.g. when dates are grouped by months, quarters,.. I found no way that I can read the current grouping settings. Looks like MS forgot to implement it. I can set with VBA or VB.net the grouping but i didn't find a straightfwd way to read the grouping settings. right click on a header of the pivot table? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get visible pivot table items
* right click on a header of the pivot table? ...I'm talking about reading the grouping with VBA or VB.net code.... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get visible pivot table items
minimaster wrote:
I have written an add-in for myself that allows to save a pivottable layout with all setttings and then I can restore this saved layout when I want to. Because there are problems to read certain filterings, as you discovered,, I'm temporarly changing the field orientation, then reading the filtering, and then restoring the field to its original orientation. I still have a one problem with the grouping of data, e.g. when dates are grouped by months, quarters,.. I found no way that I can read the current grouping settings. Looks like MS forgot to implement it. I can set with VBA or VB.net the grouping but i didn't find a straightfwd way to read the grouping settings. Hi minimaster, The grouping settings do not bother me; it's just the data filtering capabilities that the pivot is used for. However, my client has decided to forego filtering the data via a pivot table alltogether, and I'm going to build a VBA data selection userform instead. Thanks for the answer though! -- Kind regards, Carl Colijn TwoLogs - IT Services and Product Development A natural choice! http://www.twologs.com TimeTraces: the powerful and versatile time registration system! http://timetraces.twologs.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Items Visible | Excel Programming | |||
Set Pivot-Table Visible Items | Excel Programming | |||
Pivot Table Pivot Item Visible - Why so difficult | Excel Programming | |||
Unable to access visible property of Pivot Items class | Excel Programming | |||
Unable to access visible property of Pivot Items class | Excel Programming |