[XLSReadWrite4] Formulas used breaks my document

Questions and answers on how to use XLSReadWriteII 3/4/5.
Post Reply
portu
Posts: 3
Joined: Fri Sep 20, 2013 9:08 am

[XLSReadWrite4] Formulas used breaks my document

Post by 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
Site Admin
Posts: 926
Joined: Mon Jun 27, 2005 9:30 pm

Re: [XLSReadWrite4] Formulas used breaks my document

Post by larsa »

Hello

Please provide an example that I can compile. The value of "DestRealRow" is unclear. Try also to short down the sample.
Lars Arvidsson, Axolot Data
portu
Posts: 3
Joined: Fri Sep 20, 2013 9:08 am

Re: [XLSReadWrite4] Formulas used breaks my document

Post by 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
Site Admin
Posts: 926
Joined: Mon Jun 27, 2005 9:30 pm

Re: [XLSReadWrite4] Formulas used breaks my document

Post by 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.
Lars Arvidsson, Axolot Data
portu
Posts: 3
Joined: Fri Sep 20, 2013 9:08 am

Re: [XLSReadWrite4] Formulas used breaks my document

Post by portu »

Hi, thank you for your help.

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

Regards.
larsa
Site Admin
Posts: 926
Joined: Mon Jun 27, 2005 9:30 pm

Re: [XLSReadWrite4] Formulas used breaks my document

Post by larsa »

Hello

No, version 5 is not a free update.
Lars Arvidsson, Axolot Data
allanm
Posts: 2
Joined: Thu Oct 17, 2013 3:29 am

Re: [XLSReadWrite4] Formulas used breaks my document

Post by allanm »

Does this mean that it is not possible to use XLSRW to add a formula containing a user-defined function?
larsa
Site Admin
Posts: 926
Joined: Mon Jun 27, 2005 9:30 pm

Re: [XLSReadWrite4] Formulas used breaks my document

Post by larsa »

Hello

No. NETWORKDAYS is not an user defined formula.
Lars Arvidsson, Axolot Data
allanm
Posts: 2
Joined: Thu Oct 17, 2013 3:29 am

Re: [XLSReadWrite4] Formulas used breaks my document

Post by 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)
Post Reply