Convert Excel VBA for PivotChart-CodePudding

I am writing code to create an Excel PivotChart, without a visible PivotTable. Apparently, I am unable to do this via Excel_TLB, (as per another question on Stackoverflow), so I am using ComObj and Excel2010. My code create the PivotChart successfully. I am now trying to set certain filters. I have captured the relevant VBA code, but I cannot get it converted to Delphi. The VBA code is

  ActiveChart.PivotLayout.PivotTable.PivotFields( _
        "[Range].[Revenue Type Group].[Revenue Type Group]").VisibleItemsList = Array( _
        "[Range].[Revenue Type Group].&[NEW]", _
        "[Range].[Revenue Type Group].&[WORKLOAD]")

My Delphi Code (only setting one value) is

 XLApp.ActiveWorkbook.ActiveChart.PivotLayout.PivotTable.PivotFields['[Range].[Revenue Type Group].[Revenue Type Group]'].VisibleItemsList[1] := '[Range].[Revenue Type Group].&[NEW]';

While I can compile, at runtime I get "Does not support a collection" exception.

I don't see anything odd in the MS VBA doc about this... https://docs.microsoft.com/en-us/office/vba/api/excel.pivotfield.visibleitemslist

I have also TRIED using a dynamic array such as

SetLength(A1, 1);
  A1[0] := '[Range].[Revenue Type Group].&[NEW]';
  XLApp.ActiveWorkbook.ActiveChart.PivotLayout.PivotTable.PivotFields['[Range].[Revenue Type Group].[Revenue Type Group]'].VisibleItemsList := A1;

This will NOT compile with the error 'Type not allowed in Variant Dispatch call. I have tried defining A1 as array of string, array of variant and array of Olevariant.

Any ideas appreciated.

CodePudding user response：

You need to create the array with VarArrayCreate(). A regular Delphi array is not accepted when using a COM object.