Page 1 of 1
[XLSReadWrite4] Formulas used breaks my document
Posted: Fri Sep 20, 2013 9:18 am
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.
Re: [XLSReadWrite4] Formulas used breaks my document
Posted: Fri Sep 20, 2013 10:23 am
by larsa
Hello
Please provide an example that I can compile. The value of "DestRealRow" is unclear. Try also to short down the sample.
Re: [XLSReadWrite4] Formulas used breaks my document
Posted: Fri Sep 20, 2013 10:37 am
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
Re: [XLSReadWrite4] Formulas used breaks my document
Posted: Sat Sep 21, 2013 9:46 am
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.
Re: [XLSReadWrite4] Formulas used breaks my document
Posted: Mon Sep 23, 2013 9:50 am
by portu
Hi, thank you for your help.
The new update (V5) is it free for those who have purchased the V4 ?
Regards.
Re: [XLSReadWrite4] Formulas used breaks my document
Posted: Mon Sep 23, 2013 2:29 pm
by larsa
Hello
No, version 5 is not a free update.
Re: [XLSReadWrite4] Formulas used breaks my document
Posted: Thu Oct 17, 2013 3:31 am
by allanm
Does this mean that it is not possible to use XLSRW to add a formula containing a user-defined function?
Re: [XLSReadWrite4] Formulas used breaks my document
Posted: Thu Oct 17, 2013 3:48 pm
by larsa
Hello
No. NETWORKDAYS is not an user defined formula.
Re: [XLSReadWrite4] Formulas used breaks my document
Posted: Thu Oct 17, 2013 9:57 pm
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)