Copy GetPivotData Formula or Find Pivot Field Name ?

[weaverjohn]

Hi Gang:

I've run afoul again and need some assistance. Here is the issue:
Our DP folks assign agreements a unique ID number in the database. When I set up a pivot table What I see is the actual agreement number (as i understand it the 'label') and not the ID number. As an example agreement Apple1 might have a Unique ID of 456789E7. In some way I need to grab the key via vba so that I can query other element on the same agreement that are in other pivot tables.

If I copy a cell in Excel here is the getpivotdata formula I get:

=GETPIVOTDATA("[Measures].[Funds]",$A$3,"[Time]","[Time].[Accounting Month].&[2010]&[1]","[Agreement].[Agreement]","[Agreement].[Agreement].[Agreement Num].&[4.59953E5]")

Notice the last bit of the agreement Number '&[4,59953E5]" - it isn't really the acutal agreement number it's some sort of key.

Here is what I've tried so far in VBA:

  DAUnCost.Cells(16, 2).Formula = DAUnCost.Cells(11, 2).Formula
  DAUnCost.Cells(17, 2).Formula = "=B11"
  DAUnCost.Cells(18, 2).Value = DAUnCost.PivotTables("pvtUncosted").DataFields(5).PivotItems(1)
  DAUnCost.Cells(11, 3).Select
  DAUnCost.Cells(18, 2).Value = ActiveCell.PivotCell.ColumnItems(3).Name

16,2 is just the field value from the pivot table - doesn't do me any good.
17,2 is just the value of the cell again.
The remainder don't really work.

I am looping through the pivot table looking for some specific conditions. I know the row number of the data. If I could get the field value for the agreement number I could manage it from there. If I could use VBA to simply generate the GetPivotData statement I could strip what I needed from the resulting formula and manage it (although the first method is preferred)

Vlookup isn't a solid method becuase the dataset in the other tables may not match the first table (date ranges could be different and drill down could be different (showing weeks not months). So GetPivotData is methods of choice since I can get very specific about the period and know what is being returned is the correct value and that if the field isn't shown I'll get an error.

So . . . Is there a way to get the agreement key knowing the row number the data is on? Is there a way to get where I need to be in a manner that I haven't thought of?

Any assistance would be greatly appreciated.

JW