Help with Pivot Table

[JsJ_Slim]

Hi,

I need some help with pivot tables:

Given a pivot table, and a selected cell within it, is there any way I can
find out the Column Label and Row Label values that the cell is in? There's
just so many functions in the PivotTable object model (get_HiddenFields,
get_ColumnFields, get_DataFields, etc), and I've no idea where to start.

| Column Labels
| columnA | columnB |
Item1 | 6 | 2 |
+ rowA | 2 | 2 |
+ rowB | 4 | |
Item2 | 3 |12 |
+ rowB | | 4 (A) |
+ rowC | 3 | 8 |
Total | 9 | 14 |

Not sure if the diagram will load up correctly. If it does, assume that cell
(A) is selected, and hence, I'm trying to get "Item2", "rowB" and "columnB".

If the diagram is not formatted correctly, I've uploaded the image here:

http://img58.imageshack.us/img58/6053/pivottablesy5.png

Please help.

Thanks


Josh

 

[JsJ_Slim]

Hi Snake,
thanks for the attempt. However, there's a reason why I posted this in the
excel.programming newsgroup. Trying to figure out how to do this
programmatically (I'm fine with C# or VBA code). I'm using C# and the Excel
Object Model 2007.

Thanks again

Josh

 

[JsJ_Slim]

Sorry, have no idea what that is or how to do it. Can you walk me through it?

Thanks

 

[JsJ_Slim]

Hi everyone,

After going through (and testing) almost every class in the Excel 2007
reference doc that contains the word "Pivot", I think I've finally figured
out how to get the labels:

// Selected cell (in this case, the active cell)
PivotCell cell = Application.ActiveCell.PivotCell;

PivotItemList pivotRow = cell.RowItems;
PivotItemList pivotColumn = cell.ColumnItems;

// Retrieve row labels
for (int i = 1; i <= pivotRow.Count; i++)
rowLabels.Add(pivotRow.Item(i).Caption);

// Retrieve column labels
for (int i = 1; i <= pivotColumn.Count; i++)
columnLabels.Add(pivotColumn.Item(i).Caption);

Man, it was really hard to figure this one out, especially when I didn't
know all the terms and keywords.

In any case, hope this will help anyone who needs it in the future. If this
is the wrong approach, please let me know too.

Thanks!

Josh

 

[Alexandre Brisebois]

Have you found a way to get a full list of all DataFields ?