How to get the caption on a PowerPivot RowItem with VBA

[cr731]

I have a PowerPivot table and I'm using VBA to try to return the caption on certain RowItems based on the selected cell.

So if my selected cell is in the values area of my PT, I use this

ActiveCell.PivotCell.RowItems(1).Name

Which gives me the fully qualified value with Table and Column Name such as

[Sales].[Customer].[CustomerName]

However, all I really want is CustomerName.

I know I could do different things with replace or substitute to get it... but I just feel there must be same way...?

I tried

ActiveCell.PivotCell.RowItems(1).Caption

But this returns blank.

Ultimately, I am trying to return the values in the RowFields of the Pivot based on the selected PivotCell, so if there is some other, better, way, I'm not tied to using the code above.

 

[Russell Hauf]

Not sure it is worth it to try to find a different way. How about:

Replace(Mid(ActiveCell.PivotCell.RowItems(1).Name,InStrRev(ActiveCell.PivotCell.RowItems(1).Name,".")+2),"]","")

Or, maybe a cleaner way:

strCaption = ActiveCell.PivotCell.RowItems(1).Name
strCaption = Replace(Mid(strCaption,InStrRev(strCaption,".")+2),"]","")