-2

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://learn.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.

Ken White
  • 123,280
  • 14
  • 225
  • 444
user1009073
  • 3,160
  • 7
  • 40
  • 82
  • [`PivotFields`](https://learn.microsoft.com/en-us/office/vba/api/excel.pivottable.pivotfields) is a method, not an array property. – Olivier Sep 03 '21 at 13:03
  • Oliver - ShowMessage(XLApp.ActiveWorkbook.ActiveChart.PivotLayout.PivotTable.PivotFields['[Range].[Revenue Type Group].[Revenue Type Group]'].Name); works and displays as expected, so I don't think PivotFields syntax is the issue. – user1009073 Sep 03 '21 at 13:07
  • Maybe you need to use `VarArrayCreate()` (see [this](https://stackoverflow.com/questions/3619753/how-to-use-variant-arrays-in-delphi)). – Olivier Sep 03 '21 at 13:26
  • 2
    It would be much easier to answer if the question contained [MRE]. – BrakNicku Sep 03 '21 at 13:43
  • The VBA code can't be correct with an odd sum of brackets. – AmigoJack Sep 03 '21 at 13:57
  • 1
    @Olivier - VarArrayCreate did the trick. Submit as answer and I will accept. – user1009073 Sep 03 '21 at 14:09

1 Answers1

2

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

Olivier
  • 13,283
  • 1
  • 8
  • 24