Excel formula SUMIFS not working when using Delphi

When I try the program below I get
error  "OLE error 800A03EC"
When I put the formula
=SOMMEN.ALS(R[-8]K:R[-2]K;R[-8]K[-1]:R[-2]K[-1];">100";R[-8]K[-1]:R[-2]K[-1];"<200")
manualy in the Excel sheet on cell (11,2) everyting is ok.
In the attachment the test.xlsx file.

===================

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ComObj, StdCtrls;

type
  TForm1 = class(TForm)
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
var ExcelApp : variant;
begin
  ExcelApp:= CreateOleObject('Excel.Application');
  ExcelApp.DisplayAlerts := False;
  ExcelApp.Workbooks.Open('c:\users\myself\temp\test.xlsx');
  //"=SOMMEN.ALS" is Dutch version of "=SUMIFS"
  ExcelApp.ActiveWorkbook.WorkSheets[1].Cells(11,2):='=SOMMEN.ALS(R[-8]K:R[-2]K;R[-8]K[-1]:R[-2]K[-1];">100";R[-8]K[-1]:R[-2]K[-1];"<200")';
  ExcelApp.Visible:=True;
end;

end.
test.xlsx
Frans LaenenAsked:
Who is Participating?
 
Geert GConnect With a Mentor Oracle dbaCommented:
wow, now i remember again why i stopped myself from using excel for automation ...
coz,
1: it's poorly documented
2: the coding doesn't match the help, if you find any

change the ; to , in the code

ExcelApp.ActiveWorkbook.WorkSheets[1].Cells(11,2):='=SOMMEN.ALS(R[-8]K:R[-2]K,R[-8]K[-1]:R[-2]K[-1],">100",R[-8]K[-1]:R[-2]K[-1],"<200")';

Open in new window

hmm, above line doesn't work

so i changed to absolute ranges:
var   ws: Variant;  

    ws := ExcelApp.ActiveWorkbook.WorkSheets[1];
    ws.Cells(11,2) := '=SUMIFS(R1C2:R10C2,R1C1:R10C1,">100",R1C1:R10C1,"<200")';

Open in new window

0
 
Geert GOracle dbaCommented:
yeah, odd, i'm trying to figure this out too

this works:
ExcelApp.ActiveWorkbook.WorkSheets[1].Cells(11,2):='abc';

Open in new window

0
 
Frans LaenenAuthor Commented:
Also tryed "SUMIFS"  instead of "SOMMEN.ALS" but the same error
0
 
Frans LaenenAuthor Commented:
Yes!!
That does the trick!
What we learn from this is
1. Use the English version for the formulas so  "=SUMIFS" instead of "=SOMMEN.ALS" (dutch)
2. Use   ,  instead of  ;  to separate. (The formula creator in Excel uses ; to seperate.

I found that the variant ws is not neccesary.
Thanks,
Frans
0
 
Frans LaenenAuthor Commented:
Great help and saves me a lot of time.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.