Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 73
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 277
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 73
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot Items Visible LuisE Excel Programming 1 March 24th 10 10:33 AM
Set Pivot-Table Visible Items Mike H. Excel Programming 1 July 25th 08 08:59 PM
Pivot Table Pivot Item Visible - Why so difficult headly Excel Programming 8 January 5th 08 12:27 AM
Unable to access visible property of Pivot Items class Geoff Excel Programming 2 May 21st 07 03:00 AM
Unable to access visible property of Pivot Items class Geoff Excel Programming 0 May 20th 07 10:20 PM


All times are GMT +1. The time now is 10:40 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"