Pivot Table Creation - Excel

[JLovegren]

I have searched high and low for the instructions on how to create new Pivot Tables in Progress.
I have used the COM Object Viewer looking at my XL5EN32.OLB but cannot find the exact Pivot Table Creation things that my Macro Code provides.

Can anyone help with this or post a list of Pivot Table calls??
I am about to give up on this as I am running out of hair to pull out.

Below here is what the Macro Outputs:
I can select and add a sheet.
I need the ActiveWorkbook.PivotCaches.Create ... stuff
Has anyone done it - I'd really love to see your sample.
Thanks!!

Range("A1").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"BacklogDetail!R1C1:R4691C23", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion10
Sheets("Sheet4").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Cust Group")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Year")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Bucket")
.Orientation = xlColumnField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Amt Due(k)"), "Sum of Amt Due(k)", xlSum
Range("A17").Select
Sheets("BacklogDetail").Select
ActiveWorkbook.Worksheets("Sheet4").PivotTables("PivotTable1").PivotCache. _
CreatePivotTable TableDestination:="Sheet4!R15C1", TableName:="PivotTable2" _
, DefaultVersion:=xlPivotTableVersion10
Sheets("Sheet4").Select
Cells(15, 1).Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Supplier")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Year")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Bucket")
.Orientation = xlColumnField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Amt Due(k)"), "Sum of Amt Due(k)", xlSum
End Sub

 

[Osmar Morais]

I found the code below in Progress Knowledgebase site. I tryed it but didn´t work. Maybe can work for you.
The URL is http://progresscustomersupport-survey.force.com/OpenEdgeKB/articles/Article/P19744

DEFINE VARIABLE hExcel AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE hWorkbook AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE hWorksheet AS COM-HANDLE NO-UNDO.
CREATE "Excel.Application" hExcel.
hExcel:VISIBLE = TRUE.
hWorkbook = hExcel:Workbooks:Add().
hWorkSheet = hExcel:Sheets:Item(1).
hExcelisplayAlerts=FALSE.
hWorkSheet:Range("D15"):Select.
hExcel:ActiveCell:FormulaR1C1 = "33333".
hWorkSheet:Range("D16"):Select.
hExcel:ActiveCell:FormulaR1C1 = "4444".
hWorkSheet:Range("D17"):Select.
hExcel:ActiveCell:FormulaR1C1 = "666".
hWorkSheet:Range("D18"):Select.
hExcel:ActiveCell:FormulaR1C1 = "77777".
hWorkSheet:Range("E15"):Select.
hExcel:ActiveCell:FormulaR1C1 = "8888".
hWorkSheet:Range("E16"):Select.
hExcel:ActiveCell:FormulaR1C1 = "888".
hWorkSheet:Range("E17"):Select.
hExcel:ActiveCell:FormulaR1C1 = "888".
hWorkSheet:Range("E18"):Select.
hExcel:ActiveCell:FormulaR1C1 = "888".
hWorkSheet:Range("C15"):Select.
hExcel:ActiveCell:FormulaR1C1 = "fr".
hWorkSheet:Range("C16"):Select.
hExcel:ActiveCell:FormulaR1C1 = "dsss".
hWorkSheet:Range("C17"):Select.
hExcel:ActiveCell:FormulaR1C1 = "vvvv".
hWorkSheet:Range("C18"):Select.
hExcel:ActiveCell:FormulaR1C1 = "ffff".
hWorkSheet:Range("C15:E18"):Select.
hExcel:ActiveWorkbookivotCaches:Add(1,"Sheet1!R15C3:R18C5"):CreatePivotTable("R1C1","Mytable").
hExcel:ActiveSheetivotTables("Mytable"):SmallGrid = "False".
hExcel:ActiveSheetivotTables("Mytable"):AddFields ("fr","33333").
hExcel:ActiveSheetivotTables("Mytable")ivotFields("8888"):ORIENTATION = 4.
hExcel:Charts:ADD().
hExcel:ActiveChart:SetSourceData(hExcel:Worksheets("Sheet1"):Cells(1,1)).
hExcel:ActiveChart:Location(1).
RELEASE OBJECT hWorksheet.
RELEASE OBJECT hWorkbook.
RELEASE OBJECT hExcel.

 

[Osmar Morais]

After some tests I got this code that works:

hExcel:ActiveWorkbookivotCaches:ADD(1,"Plan1!R15C3:R18C5"):CreatePivotTable("","Mytable").
hExcel:ActiveSheetivotTables("Mytable"):AddFields("fr","33333").
hExcel:ActiveSheetivotTables("Mytable")ivotFields("8888"):ORIENTATION = 4.

Replace this part in the code above. I hope help someone, but I still have many problems for automatically building a pivottable, then if you have a related solution, please, share with me.

Thanks!