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; 
