I am trying to automate a series of PivotCharts within Excel. I am using Delphi Rio and Excel 2019. I have an XLSX file, with one sheet, called 'Sheet1'. I have enabled record macro within Excel, and recorded the VBA code. I have ported it to Delphi. Everything compiles, and I get an AV when I go to add my row fields (which becomes Axis fields in the PivotChart). In Excel, to record the macro, I select my sheet with my data, go to Insert/Pivot Chart, checking 'Add Data to Data Model', and then build a simple PivotChart.
The VBA code is
Cells.Select
Workbooks("TestFile.xlsx").Connections.Add2 "WorksheetConnection_Sheet1!$A:$CU" _
, "", "WORKSHEET;S:\Temp\[TestFile.xlsx]Sheet1", "Sheet1!$A:$CU", 7, True, _
False
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
ActiveWorkbook.Connections("WorksheetConnection_Sheet1!$A:$CU"), Version:=7). _
CreatePivotChart(ChartDestination:="Sheet3").Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.ChartStyle = 201
With ActiveChart.PivotLayout.PivotTable.CubeFields("[Range].[Fiscal Quarter]")
.Orientation = xlRowField
.Position = 1
End With
With ActiveChart.PivotLayout.PivotTable.CubeFields("[Range].[Territory Name]")
.Orientation = xlColumnField
.Position = 1
End With
ActiveChart.PivotLayout.PivotTable.CubeFields.GetMeasure "[Range].[Pipeline]", _
xlSum, "Sum of Pipeline"
ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
PivotTable.CubeFields("[Measures].[Sum of Pipeline]"), "Sum of Pipeline"
The problem I run into (when running my Delphi compiled executable' is when adding the 'Fiscal Quarter' rowfield. It APPEARS that CubeFields is not defined, although I get 'data inaccessible' when I mouse over it. So my first question, in Delphi, is CubeFields created automatically?
My Delphi code is
procedure BuildPivotChart;
var
myChart: _Chart;
myChartShape: Shape;
myPivotCache: PivotCache;
SourceSheet, DestSheet: _Worksheet;
myConnection: WorkbookConnection;
ConnName, ConnString, ConnCommand: String;
begin
// Create the connection since we are adding to DataModel
ConnName := 'WorksheetConnection_Sheet1!$A:$CU';
ConnString := 'WORKSHEET;S:\Temp\[TestFile.xlsx]Sheet1';
ConnCommand := 'Sheet1!$A:$CU';
myConnection := oExcel.ActiveWorkbook.Connections.Add2(ConnName, '', ConnString, ConnCommand, 7, True, False);
// Create a new Sheet, and get a handle to it...
oExcel.Worksheets.Add(EmptyParam, EmptyParam,1, xlWorksheet, LCID );
// Get a handle to the new sheet and set the Sheet Name
DestSheet := oExcel.ActiveSheet as _Worksheet;
DestSheet.Name := 'Graphs';
// Create a Pivot Cache, with a Chart object
myPivotCache := oExcel.ActiveWorkbook.PivotCaches.Create(xlExternal, myConnection, 7);
myChartShape := myPivotCache.CreatePivotChart(DestSheet, xlColumnClustered, 10, 10, 300, 300);
myChartShape.Select(EmptyParam);
myChart := oExcel.ActiveChart;
myChart.ChartStyle := 201;
if oExcel.ActiveChart.PivotLayout.PivotTable.PivotCache.IsConnected = True then
ShowMessage('Connected');
// Now add Row Fields
// ALL THREE OF THE BELOW LINES gives AV
oExcel.ActiveChart.PivotLayout.PivotTable.CubeFields.AddSet('[Range].[Fiscal Quarter]', 'MyName').Orientation := xlRowField;
oExcel.ActiveChart.PivotLayout.PivotTable.CubeFields['[Range].[Fiscal Quarter]'].Orientation := xlRowField;
// Thinking that the CUBE is not defined... check to see cube fields count..
ShowMessage(IntToStr(oExcel.ActiveChart.PivotLayout.PivotTable.CubeFields.Count));
...
When I step through my Delphi code, on the 'myPivotCache.CreatePivotChart' line, I see the chart object show up in Excel, and I see my columns listed in the Pivot selected on the right side of Excel. I have a suspicion that something is wrong with CubeFields, but I can't prove it, or I don't know how to fix it... I DO get my 'Connected' popup.
Any help or ideas appreciated.
