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;