For who may interest, this a piece of code I made that creates a Pivot Chart based on a Pivot Table automatically from Lotus Script.
It is easy to start from here and adapt the code to your needs. It took me a while to make it works but information gathered from here helped a lot.
It works in my Windows XP and Office 2003.
-----------------CODE
Dim xlSum As Variant
Dim xlPageField As Variant
Dim xlColumnField As Variant
Dim SourceData As Variant
Dim PivotCache As Variant
Dim xlChart As Variant
xlSum = -4157
xlColumnField = 2
xlPageField = 3
Set xlWbook = xlApp.Workbooks(1)
’ here we select the range of data that will be used as origin of the Pivot Table
XlApp.range("A3:R"+Trim(Str$(j-1))).Select
With xlsheet.PivotTableWizard
’ .SourceData = (“MPU!R3C1:R99C18”) <— Not needed
.Name = "PivotTable"
.RowGrand = True
.ColumnGrand = True
End With
xlWbook.Worksheets("Sheet4").Activate
xlWbook.Worksheets("Sheet4").Name = "PivotSheet"
’ This is the action of dropping one field in the Pivot Table and summarize it
xlSum = -4157
With xlWbook.Worksheets("PivotSheet").PivotTables("PivotTable").PivotFields("Hours")
.Orientation = "4" '4 = xlDataField
.Name = "Sum of Hours"
.Function = xlSum
End With
’ Setting the format for this cell to HH:MM:SS
XlApp.range("B2").Select
With XlApp.Selection
.NumberFormat = "[h]:mm:ss;@"
End With
’ Creation of PIVOT CHART from the exisitng Pivot Table
Set xlChart = xlApp.Charts.Add
xlChart.SetSourceData xlWbook.Worksheets("PivotSheet").Range("A1")
xlChart.Location 1
’ This is to put the SUBS (subsidiary) as Page field
With xlChart.PivotLayout.PivotTable.PivotFields("Subs")
.Orientation = xlPageField
.Position = 1
End With
’ This is to put the Intervention Type in the Right field
With xlChart.PivotLayout.PivotTable.PivotFields("Intervention Type")
.Orientation = xlColumnField
.Position = 1
End With
-----------------END OF CODE
I hope it will be useful out there!
Best Regards