Avatar of Frans Laenen
Frans Laenen asked on

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
DelphiMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Frans Laenen

8/22/2022 - Mon
Geert G

yeah, odd, i'm trying to figure this out too

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

Open in new window

ASKER
Frans Laenen

Also tryed "SUMIFS"  instead of "SOMMEN.ALS" but the same error
ASKER CERTIFIED SOLUTION
Geert G

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Frans Laenen

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
Frans Laenen

Great help and saves me a lot of time.