Link to home
Start Free TrialLog in
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
Avatar of Geert G
Geert G
Flag of Belgium image

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

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

Open in new window

Avatar of Frans Laenen
Frans Laenen

ASKER

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

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Great help and saves me a lot of time.