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

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