Excel - How to find location (i.e. what columns) of specific PivotFields in a PivotTable?

Viewed 78

I am building a routine that automates creating a PivotTable. All is working well...I add my rows, columns, filters and values. As a last step, I want to format the values. I cannot find any property that shows me what "value entry/pivotfield" ends up in which column. Lets assume that I have 2 Value PivotFields...

Pivot1 := fmyPivotTable.AddDataField( 'Revenue', 'Revenue', xlSum );
Pivot2 := fmyPivotTable.AddDataField( 'Locations', 'Locations', xlCount );

I am looking for something that will tell me that Pivot1 data is in columns C, E and G while Pivot2 data is in D,F and H. The closest I could find is labelrange and position, but neither of these is really what I need. I am writing this in Delphi, but if someone knows how to do this in VBA, I can convert.

ADDITIONAL INFO. The code I am using looks similar to this. (I had to cut out some irrelevant stuff). What I am looking for is a way to determine what columns will hold the data from v1 and v2.

  var
    // Filter Fields
      f1, f2: OleVariant;
      // Row Fields
      r1, r2 : OleVariant;
      // Column Fields
      c1: OleVariant;
      // Value Fields
      v1, v2: OleVariant;
      PivotStartAddr: String;
      OrigDataRange: ExcelRange;      
      myPivotCache: PivotCache;
      myPivotTable: PivotTable; 
      RawSheet, PivotSheet1 : _Worksheet;


    begin

      RawSheet := oExcel.ActiveSheet as _Worksheet;
      OrigDataRange := RawSheet.Range['A1', 'BA35285'];

      // Create a Pivot Cache
      myPivotCache := oExcel.ActiveWorkbook.PivotCaches.Create(xlDatabase,OrigDataRange,xlPivotTableVersion15);

      // Create a New Sheet 
      oExcel.Worksheets.Add(EmptyParam, RawSheet, 1, xlWorksheet, LOCALE_USER_DEFAULT);
      PivotSheet1 := oExcel.ActiveSheet as _Worksheet;
      PivotSheet1.Name := 'Pivot Sheet';

     // Create a Pivot table within the PivotCache
       PivotStartAddr :=  'R4C1';
       myPivotTable := myPivotCache.CreatePivotTable(PivotStartAddr, 'PivotTable56', EmptyParam,xlPivotTableVersion15);

       // Add Filter Fields
       f1 :=  myPivotTable.PivotFields('OVI_MAJOR');
       f1.Caption := 'Industry';
       f1.Orientation := xlPageField;
       f1.Position :=1 ;

       f2 :=  myPivotTable.PivotFields('APPS_ORG_TYPE');
       f2.Caption := 'Apps';
       f2.Orientation := xlPageField;
       f2.Position :=2 ;

       // Add Rows  
       r1 :=  myPivotTable.PivotFields('PARENT_ACCOUNT_NAME');
       r1.Caption := 'l Name';
       r1.Orientation := xlRowField;
       r1.Position := 1;

       r2 :=  myPivotTable.PivotFields('ACCOUNT');
       r2.Caption := 'Account';
       r2.Orientation := xlRowField;
       r2.Position := 2;

       // Add Columns
       c1 :=  myPivotTable.PivotFields('CLOUD_MARKETING_SEGMENT');
       c1.Caption := 'Mkt Segment';
       c1.Orientation := xlColumnField;
       c1.Position := 1;


       v1 := myPivotTable.AddDataField('SIC', 'SIC', xlCount);
       v2 := myPivotTable.AddDataField('REVENUE', 'Revenue', xlSum);
       end;
0 Answers
Related