Sum of the All Columns in Entercell function

Dear all,

Using Nav 2013 r2.

Can you please help on, how to calculate sum of columns values with entercell function while exporting to excel.

I need to calculate all C,D,E,F,G,H,I,J values in Total(L) Column for below format.

This is my code format

==========================================================
IF LoanRecN.FINDSET THEN
REPEAT
Col:=1;
EnterCell(Row,Col,FORMAT(LoanRecN.“Date of Maturity”),FALSE,FALSE,FALSE,1);
Col+=1;
EnterCell(Row,Col,LoanRecN.Company,FALSE,FALSE,FALSE,1);
Col+=1;
BankRec.RESET;
IF BankRec.FINDFIRST THEN
REPEAT
IF LoanRecN.Bank = BankRec.“No.” THEN BEGIN
LoanRecN.CALCFIELDS(“Loan Outstanding Amount”);
EnterCell(Row,Col,FORMAT(LoanRecN.“Loan Outstanding Amount”),FALSE,FALSE,FALSE,1);
NairaAmt += LoanRecN.“Loan Outstanding Amount”;
Col +=1;
END ELSE BEGIN
Col +=1;
END;
UNTIL BankRec.NEXT = 0;
EnterCell(Row,Col,’=SUM(’+ExcelCol(3)+FORMAT(Row)+’:’+ExcelCol(Col-1)+FORMAT(Row)+’)’,TRUE,FALSE,FALSE,1);
Row +=1;
UNTIL LoanRecN.NEXT =0;

==========================================

And ExcelCol function is

==========================================

ExcelCol(CN : Integer) : Text[10]
xlColID := ‘’;
WHILE x > 26 DO BEGIN
y := x MOD 26;
IF y = 0 THEN
y := 26;
c := 64 + y;
i := i + 1;
t[i] := c;
x := (x - y) DIV 26;
END;
IF x > 0 THEN BEGIN
c := 64 + x;
i := i + 1;
t[i] := c;
END;
FOR x := 1 TO i DO
xlColID[x] := t[1 + i - x];

=======================================

My output is

=======================================

Hi,

did you have tried “=Sum(” instead of “=SUM(” ?

I think, this should solve the problem, if the rest of your code is correct!

Please try this out.

best regards

Robert

No Robert,

Still I am getting same output.

|



|
| - |
| |
| |
| |

There must be an issue, with the language of system, excel and dynamics nav. I have expierenced, that in a german environment =Summe() didn’t work.

It works for us, if we change it to =Sum(). I can only guess, that you try some different commands.

Maybe you can also try the function “AddColumn” of the excel buffer. I can confirm, that addcolumn works with a formula:

ExcelBuffer.AddColumn(’=Sum(A1:a5)’,TRUE,’’,FALSE,FALSE,FALSE,’’,0);

If this doesn’t work, I would give you an object which is working for me with sum. So maybe you get the problem then.

Robert Thanks for your response.

Can you please send me the object.

Thanks.

Hi,

i have created a report in nav 2013 R2 with an excel sum! I have put the txt and fob in the zip. Also I have attached my result, after running the report.

http://robert-dynamicsnav.de/wp-content/uploads/2016/03/Test_SUM.zip

best regards

Robert