Excel pivot fields selected issue

[ejsmith]

I'm trying to use vba (in Access) to determine which page fields have been selected in an Excel pivot table. Specifically I need to determine which fields have been hidden (or conversely are showing) in a specific page field. Problem is that some of the fields are hidden by double clicking on the page field and highlighting some of them in 'Hide Items:' box that appears, and no one particular item is selected using the normal frop-down list.
For some reason when I run through the items in the page field they all show as 'Visible = False' despite the fact that I'm certain that only a few of them have been hidden.

Here is how I'm looking at it:
For Each pf In X.ActiveSheet.PivotTables (PivotTableName).PageFields
pf.ShowAllItems = True
For Each pi In pf.PivotItems
Debug.Print "Page Field: " & pf.Name & "; Item: " & pi.Name & "; Visible: " & pi.Visible & "; Value: " & pi.Value

Next
Next

I have already cleaned out the cache (using prior answers from this forum). I'd appreciate any help!
Thanks in advance!

 

[SkipVought]

ejsmith,

First you want to UNHIDE the Spreadsheet Rows and then evaluate the PivotTable.

ActiveSheet.Cells.Rows.Hidden = False
For Each pf In X.ActiveSheet.PivotTables (PivotTableName).PageFields
        For Each pit In pf.PivotItems
          if pit.visible then

          else

          end if
        Next
    Next

BTW,

NEVER use pi as a variable! pi == 3.14159.........

;-)

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ejsmith]

Thanks Skip - that should work!