CODEWITHSUNDEEP
exceldelphi
Ali Dehban
Ali Dehban

Reputation: 148

How to correctly set Excel NumberFormat property for Formula with Delphi?

When automating Excel via OLE from my Delphi program and trying to set a cell's NumberFormat property to 'General' and Writing the Formula , Excel is raising exception with this Notification :

Project X.exe raised exception class EVariantInvalidOpError with message 'Invalid variant operation'.

In the other words My question is that : Which NumberFormat is the best format for Formula Cells containing text function like concatenate?

here is my sample code:

USE System.Win.ComObj;

procedure TForm1.Btn_1Click(Sender: TObject);
var
  XL: variant;
  Sheet,
  WorkBook: variant;
begin
  try
    XL := GetActiveOleObject('Excel.Application');
  except
    XL := CreateOleObject('Excel.Application');
  end;
  XL.Visible := False;
  WorkBook:=XL.WorkBooks.Add;
  Sheet:=XL.WorkBooks[1].WorkSheets.Add;
  Sheet.Name := 'Test-Sheet';
  Sheet.Cells[1, 1]:='col1';
  Sheet.Cells[1, 1].Font.Bold := True;
  Sheet.Cells[1, 1].Font.Size := 10;

  Sheet.Cells[1, 2]:='col2';
  Sheet.Cells[1, 2].Font.Bold := True;
  Sheet.Cells[1, 2].Font.Size := 10;

  Sheet.Cells[1, 3]:='Basic Formula';
  Sheet.Cells[1, 3].Font.Bold := True;
  Sheet.Cells[1, 3].Font.Size := 10;
  Sheet.Cells[1, 3].numberformat := 'General';


  Sheet.Cells[1, 4]:='Text function ';
  Sheet.Cells[1, 4].Font.Bold := True;
  Sheet.Cells[1, 4].Font.Size := 10;
  Sheet.Cells[1, 4].numberformat := 'General';

  Sheet.Cells[2, 1].Value := '1000';
  Sheet.Cells[2, 2].Value := '500'; // or := 'abcxyz' for concat
  Sheet.Cells[2, 3].Value := '=A2+B2'; //this one is ok
  Sheet.Cells[2, 4].Value := '=CONCATENATE(A2;B2)'; //this one raised exception...

  XL.WorkBooks[XL.WorkBooks.Count].SaveAs(  ExtractFileDir( Application.ExeName ) +'\Test.xls' ,51 );
  XL.ActiveWorkbook.Close(0);
  XL.Quit;
end;

Upvotes: 1

Views: 2712

Answers (2)

Tom Brunberg
Tom Brunberg

Reputation: 21033

From documentation:

To combine text and numbers, use the CONCATENATE function, the ampersand operator (&), or the TEXT function and the ampersand operator.

When you use the CONCATENATE() function, the arguments are separated with commas(,), not semicolons(;), alternatively with ampersands(&)

Upvotes: 1

Алексей Мокрев
Алексей Мокрев

Reputation: 192

Try this

Sheet.Cells[2, 4].Formula := '=Sum(A1:A10)';

or

Sheet.Range['A11', 'A11'].Formula := '=Sum(A1:A10)';

Upvotes: 1

Related Questions