[XLSReadWrite4] Formulas used breaks my document

[portu]

Hi all,

I try to write a formulas into the cell. I can save the file but if I open this from Excel, Excel tell me the file contains a bad character.
The problem come of this function : NETWORKDAYS.

Code: Select all

...
DestXLSDoc.Sheet[0].AsFormula[40,DestRow] := 'IF(OR(A'+DestRealRow+'=""; ISTEXT(A'+DestRealRow+'));"";IF(NOT(AN'+DestRealRow+'="X");"NON";IF(ISERR(DAY(AM'+DestRealRow+'));"";IF(NETWORKDAYS(AM'+DestRealRow+';A'+DestRealRow+')>2;"OUI";"NON"))))';
...

If I remove this function, this formulas work fine.
Have you any solution of this problem please ?

Thank you for your help.

[larsa]

Hello

Please provide an example that I can compile. The value of "DestRealRow" is unclear. Try also to short down the sample.

[portu]

This is a short test that doesn't work :

Code: Select all

procedure TestXls;
var
DestXLSDoc : TXLSReadWriteII4;
begin
  DestXLSDoc := TXLSReadWriteII4.Create(nil);
  try
    DestXLSDoc.Version := xvExcel2007;
    DestXLSDoc.Filename := 'c:\test.xlsx';
    DestXLSDoc.Read;
    DestXLSDoc.Sheet[0].Rows.AddIfNone(0,1);
    DestXLSDoc.Sheet[0].AsFormulaRef['A1'] := 'NETWORKDAYS(C1;C2)';
    DestXLSDoc.Write;
  finally
    DestXLSDoc.Free;
  end;
end;

PS : Don't forget to create an Excel file that contains two cells (C1 and C2) with a date.
Thank you

[larsa]

Hello

The problem is the function NETWORKDAYS. XLSReadWriteII v.4 has only the formulas available in Excel 97. XLSReadWriteII v.5 has support for all formulas.

[portu]

Hi, thank you for your help.

The new update (V5) is it free for those who have purchased the V4 ?

Regards.

[larsa]

Hello

No, version 5 is not a free update.

[allanm]

Does this mean that it is not possible to use XLSRW to add a formula containing a user-defined function?

[larsa]

Hello

No. NETWORKDAYS is not an user defined formula.

[allanm]

Thanks Lars, but I was asking about a user-defined function in general. Say I have a VBA (or add-in) function MYFUNCTION. Is there any way of using XLSRW to add a formula that includes MYFUNCTION? e.g. =A1+MYFUNCTION(B2)