Dynamics Community Forum Thread Details

Hello sir,

My requirement is i want to import 2 fields from table No.273 (i.e Bank account No,statement No.) and other fields from excel into bank acc. reconciliation line table.For this i have created and report .PFB code for the same.

Documentation()

OnInitReport()

OnPreReport()
{
ExcelBuffer.LOCKTABLE;
ExcelBuffer.OpenBook(ServerFilename,SheetName);
ExcelBuffer.ReadSheet;
GetLastRowandColumn;

FOR X:=14 TO Totalrows DO
InsertData(X);
ExcelBuffer.DELETEALL;
MESSAGE('Import Completed.');
}

OnPostReport()

Bank Acc. Reconciliation - OnPreDataItem()

Bank Acc. Reconciliation - OnAfterGetRecord()

Accountnovar:="Bank Acc. Reconciliation"."Bank Account No.";
Statementvar:="Bank Acc. Reconciliation"."Statement No.";
//MESSAGE(Accountnovar);
//MESSAGE(Statementvar);
ExcelBuffer.LOCKTABLE;
ExcelBuffer.OpenBook(ServerFilename,SheetName);
ExcelBuffer.ReadSheet;
GetLastRowandColumn;

FOR X:=14 TO Totalrows DO
InsertData(X);
ExcelBuffer.DELETEALL;
MESSAGE('Import Completed.')

Bank Acc. Reconciliation - OnPostDataItem()

Excel Buffer - OnPreDataItem()

Excel Buffer - OnAfterGetRecord()

Excel Buffer - OnPostDataItem()

GetLastRowandColumn()
ExcelBuffer.SETRANGE("Row No.",1);
Totalcolumn:=ExcelBuffer.COUNT;
ExcelBuffer.RESET;
IF ExcelBuffer.FINDLAST THEN
Totalrows:=ExcelBuffer."Row No.";

OpenBook(FileName : Text;SheetName : Text[250])
IF FileName = '' THEN
ERROR(Text001);
IF FileName = '' THEN
ERROR(Text002);

XlWrkBkReader := XlWrkBkReader.Open(FileName);
IF XlWrkBkReader.HasWorksheet(SheetName) THEN BEGIN
XlWrkShtReader := XlWrkBkReader.GetWorksheetByName(SheetName);
END ELSE BEGIN
QuitExcel;
ERROR(Text004,SheetName);
END;

QuitExcel()

// Close and clear the OpenXml book
CloseBook;

// Clear the worksheet automation
IF NOT ISNULL(XlWrkSht) THEN
CLEAR(XlWrkSht);

// Clear the workbook automation
IF NOT ISNULL(XlWrkBk) THEN
CLEAR(XlWrkBk);

// Clear and quit the Excel application automation
IF NOT ISNULL(XlApp) THEN BEGIN
XlHelper.CallQuit(XlApp);
CLEAR(XlApp);
END;

CloseBook()

IF NOT ISNULL(XlWrkBkWriter) THEN BEGIN
XlWrkBkWriter.ClearFormulaCalculations;
XlWrkBkWriter.ValidateDocument;
XlWrkBkWriter.Close;
CLEAR(XlWrkShtWriter);
CLEAR(XlWrkBkWriter);
END;

IF NOT ISNULL(XlWrkBkReader) THEN BEGIN
CLEAR(XlWrkShtReader);
CLEAR(XlWrkBkReader);
END;

InsertData(RowNo : Integer)

BankaccReconciline."Statement Line No." +=10000;
//EVALUATE(BankaccReconciline."Statement Line No.",GetvalueAtCell(RowNo,1));
EVALUATE(BankaccReconciline."Transaction Date",GetvalueAtCell(RowNo,2));
EVALUATE(BankaccReconciline.Description,GetvalueAtCell(RowNo,3));
EVALUATE(BankaccReconciline."Check No.",GetvalueAtCell(RowNo,5));

IF GetvalueAtCell(RowNo,6) <> '' THEN
//bgk := GetvalueAtCell(RowNo,6)) * -1
EVALUATE(BankaccReconciline."Statement Amount",GetvalueAtCell(RowNo,6))
ELSE
IF GetvalueAtCell(RowNo,7) <> '' THEN BEGIN
EVALUATE(BankaccReconciline."Statement Amount",GetvalueAtCell(RowNo,7));
BankaccReconciline."Statement Amount":=BankaccReconciline."Statement Amount"*(-1);
END;
{
IF GetvalueAtCell(RowNo,6) = '' THEN
//bgk := GetvalueAtCell(RowNo,6)) * -1
EVALUATE(BankaccReconciline."Statement Amount",GetvalueAtCell(RowNo,7));

IF GetvalueAtCell(RowNo,7) = '' THEN
EVALUATE(BankaccReconciline."Statement Amount",GetvalueAtCell(RowNo,6));
}

//EVALUATE(BankaccReconciline."Applied Amount",GetvalueAtCell(RowNo,7));
EVALUATE(BankaccReconciline."Value Date",GetvalueAtCell(RowNo,9));
BankaccReconciline."Bank Account No." := Accountnovar;
BankaccReconciline."Statement No." :=Statementvar;

BankaccReconciline.INSERT(TRUE);

GetvalueAtCell(RowNo : Integer;Column : Integer) : Text
IF NOT ExcelBuffer1.GET(RowNo,Column)
THEN EXIT('') ELSE
EXIT(ExcelBuffer1."Cell Value as Text");
{
ExcelBuffer1.GET(RowNo,Column);
EXIT(ExcelBuffer1."Cell Value as Text");
}

Not able to retrieve data from Bank acc. reconcilation Table

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Suggested Answers

Leaderboard

Featured topics

Product updates