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

 

[Snake Plissken]

You can always double-click on selected cell so data that stands behind that
firgure will be displayed. I dont know if it helps.

S.

 

[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

 

[Snake Plissken]

did you try to analyze auto-recorded makro during which the data is shown? I
know - this might be a childish answer but still there is a reason to start
with the simpliest solution

 

[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

 

[Snake Plissken]

If you have MS Office 2003 or older then you have to chose
"Tools->macro->Record New Macro". Then you have to proceed with things you
would like to do programatically ( but still you do it manually ). After
it's done you have to stop recording the macro in the same place you started
it. Then you have to analize the code generated automatically by excel. The
only thing you need is to optimize it or change it according to your needs.

 

[Snake Plissken]

yeah - you may go through the references, classess etc ro try to analize
automatic code I told you some minutes ago. Depending on the situation it
might bring good results as well.

 

[Alexandre Brisebois]

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