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
=======================================
