Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / Aging to Excel Buffer,...
Microsoft Dynamics NAV (Archived)

Aging to Excel Buffer, 'Excel Buffer already exist identification field =1 and value=1

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

OK guys, I am using excel buffer and; excel buffer is temporary, I have done the delete all and the reset and I am still getting the error.  I have looked in the Excel buffer and deleted all the records.  Can you tell me what else to check.  Code below

PreReport()

ExcelBuf.DELETEALL;

OnPostReport()
ExcelBuf.RESET;
IF PrintToExcel THEN
CreateExcelbook;
ExcelBuf.GiveUserControl;

MakeExcelInfo()
MakeExcelDataHeader;
ExcelBuf.SetUseInfoSheet;
ExcelBuf.AddInfoColumn(FORMAT(Text103),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text);
ExcelBuf.AddInfoColumn(CompanyInformation.Name,FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text); //Company Name
ExcelBuf.NewRow;
ExcelBuf.AddInfoColumn(FORMAT(Text105),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text); //Report Name
ExcelBuf.AddInfoColumn(FORMAT(Text102),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text);
ExcelBuf.NewRow;
ExcelBuf.AddInfoColumn(FORMAT(Text104),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text);
ExcelBuf.AddInfoColumn(REPORT::"Aging 360 Days test",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Number); //Title
ExcelBuf.NewRow;
ExcelBuf.AddInfoColumn(FORMAT(Text106),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text);
ExcelBuf.AddInfoColumn(USERID,FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text); //UserID
ExcelBuf.NewRow;
ExcelBuf.AddInfoColumn(FORMAT(Text107),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text);
ExcelBuf.AddInfoColumn(TODAY,FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Date); //Date
ExcelBuf.AddInfoColumn(TIME,FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Time); //Time
ExcelBuf.NewRow;
ExcelBuf.AddInfoColumn(FORMAT(Text108),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text);
ExcelBuf.AddInfoColumn(FilterString,FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text); //Filterstring
ExcelBuf.NewRow;
ExcelBuf.AddInfoColumn(FORMAT(Text109),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text); //Text109 = 'Aged by'
ExcelBuf.AddInfoColumn(DateTitle,FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text); //Date Title
ExcelBuf.NewRow;
ExcelBuf.AddInfoColumn(FORMAT(Text113),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text);
ExcelBuf.AddInfoColumn(PeriodEndingDate[1],FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Date); //Aging as of Date
ExcelBuf.NewRow;
ExcelBuf.AddInfoColumn(FORMAT(Text110),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text); // Text110 = 'Amounts are'
IF PrintAmountsInLocal THEN
ExcelBuf.AddInfoColumn(FORMAT(Text112),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text) //Text112 = 'As indicated in Data'
ELSE
ExcelBuf.AddInfoColumn(FORMAT(Text111),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text); //Text111 = 'In our Functional Currency'
ExcelBuf.ClearNewRow;

MakeExcelDataHeader()
ExcelBuf.NewRow;
ExcelBuf.AddColumn('Cust No.',FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuf."Cell Type"::Text);
ExcelBuf.AddColumn('Customer Name',FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuf."Cell Type"::Text);
ExcelBuf.AddColumn('Job No.',FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuf."Cell Type"::Text);
ExcelBuf.AddColumn('T',FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuf."Cell Type"::Text);
ExcelBuf.AddColumn('Doc. No.',FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuf."Cell Type"::Text);
ExcelBuf.AddColumn('Posting Date',FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuf."Cell Type"::Text);
ExcelBuf.AddColumn('Balance Due',FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuf."Cell Type"::Text);
ExcelBuf.AddColumn('Current',FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuf."Cell Type"::Text);
ExcelBuf.AddColumn('31 - 60',FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuf."Cell Type"::Text);
ExcelBuf.AddColumn('61 - 90',FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuf."Cell Type"::Text);
ExcelBuf.AddColumn('91 - 120',FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuf."Cell Type"::Text);
ExcelBuf.AddColumn('121 - 180',FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuf."Cell Type"::Text);
ExcelBuf.AddColumn('181 - 240',FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuf."Cell Type"::Text);
ExcelBuf.AddColumn('241 - 360',FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuf."Cell Type"::Text);
ExcelBuf.AddColumn(' 360 +',FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuf."Cell Type"::Text);
ExcelBuf.AddColumn('Retain.',FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuf."Cell Type"::Text);
ExcelBuf.AddColumn('Dpt.',FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuf."Cell Type"::Text);
ExcelBuf.AddColumn('CG',FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuf."Cell Type"::Text);

MakeExcelDataBody()
ExcelBuf.NewRow;
ExcelBuf.AddColumn("Cust. Ledger Entry"."Customer No.",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text);
ExcelBuf.AddColumn(Customer.Name,FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text);
ExcelBuf.AddColumn(SI."Job No.",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text);
ExcelBuf.AddColumn("Cust. Ledger Entry"."Document Type",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text); //***********substring
ExcelBuf.AddColumn("Cust. Ledger Entry"."Document No.",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text);
ExcelBuf.AddColumn("Cust. Ledger Entry"."Posting Date",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Date);
ExcelBuf.AddColumn(AmountDueToPrint,FALSE,'',FALSE,FALSE,FALSE,'#,##0.00',ExcelBuf."Cell Type"::Number); //BALANCE DUE
ExcelBuf.AddColumn(AmountDue[1],FALSE,'',FALSE,FALSE,FALSE,'#,##0.00',ExcelBuf."Cell Type"::Number); //CURRENT
ExcelBuf.AddColumn(AmountDue[2],FALSE,'',FALSE,FALSE,FALSE,'#,##0.00',ExcelBuf."Cell Type"::Number);
ExcelBuf.AddColumn(AmountDue[3],FALSE,'',FALSE,FALSE,FALSE,'#,##0.00',ExcelBuf."Cell Type"::Number);
ExcelBuf.AddColumn(AmountDue[4],FALSE,'',FALSE,FALSE,FALSE,'#,##0.00',ExcelBuf."Cell Type"::Number);
ExcelBuf.AddColumn(AmountDue[5],FALSE,'',FALSE,FALSE,FALSE,'#,##0.00',ExcelBuf."Cell Type"::Number);
ExcelBuf.AddColumn(AmountDue[6],FALSE,'',FALSE,FALSE,FALSE,'#,##0.00',ExcelBuf."Cell Type"::Number);
ExcelBuf.AddColumn(AmountDue[7],FALSE,'',FALSE,FALSE,FALSE,'#,##0.00',ExcelBuf."Cell Type"::Number);
ExcelBuf.AddColumn(AmountDue[8],FALSE,'',FALSE,FALSE,FALSE,'#,##0.00',ExcelBuf."Cell Type"::Number);
ExcelBuf.AddColumn(CustRetainage,FALSE,'',FALSE,FALSE,FALSE,'#,##0.00',ExcelBuf."Cell Type"::Number);
ExcelBuf.AddColumn("Cust. Ledger Entry"."Global Dimension 1 Code",FALSE,'',FALSE,FALSE,FALSE,'#,##0.00',ExcelBuf."Cell Type"::Number);
ExcelBuf.AddColumn("Cust. Ledger Entry"."Customer Posting Group",FALSE,'',FALSE,FALSE,FALSE,'#,##0.00',ExcelBuf."Cell Type"::Number);

CreateExcelbook()
ExcelBuf.CreateBookAndOpenExcel('Sheet 1','Aging 360',COMPANYNAME,USERID);

*This post is locked for comments

  • Rajasekhar@MS Profile Picture
    Rajasekhar@MS 5,567 on at
    RE: Aging to Excel Buffer, 'Excel Buffer already exist identification field =1 and value=1

    Hi Kim

    The Aged Account Payables for reference how excel has done in NAV, in your case activate the debugger and find why the Row / Column no are not increasing, then increment the Row / Column no by 1 which will solve the problem

    Regards

    Rajasekhar.Y

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Aging to Excel Buffer, 'Excel Buffer already exist identification field =1 and value=1

    No it is not like that this is just a line by line aged listing no percents or grouping by customers

  • Rajasekhar@MS Profile Picture
    Rajasekhar@MS 5,567 on at
    RE: Aging to Excel Buffer, 'Excel Buffer already exist identification field =1 and value=1

    Can you check the Aged Accounts Payable Report, it is similar to yours

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Aging to Excel Buffer, 'Excel Buffer already exist identification field =1 and value=1

    now it is row 2 column 1

  • Rajasekhar@MS Profile Picture
    Rajasekhar@MS 5,567 on at
    RE: Aging to Excel Buffer, 'Excel Buffer already exist identification field =1 and value=1

    Hi

    Replace the Makeexcelinfo as below & check

    MakeExcelInfo()

    ExcelBuf.SetUseInfoSheet;

    ExcelBuf.AddInfoColumn(FORMAT(Text103),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text);

    ExcelBuf.AddInfoColumn(CompanyInformation.Name,FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text); //Company Name

    ExcelBuf.NewRow;

    ExcelBuf.AddInfoColumn(FORMAT(Text105),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text); //Report Name

    ExcelBuf.AddInfoColumn(FORMAT(Text102),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text);

    ExcelBuf.NewRow;

    ExcelBuf.AddInfoColumn(FORMAT(Text104),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text);

    ExcelBuf.AddInfoColumn(REPORT::"Aging 360 Days test",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Number); //Title

    ExcelBuf.NewRow;

    ExcelBuf.AddInfoColumn(FORMAT(Text106),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text);

    ExcelBuf.AddInfoColumn(USERID,FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text); //UserID

    ExcelBuf.NewRow;

    ExcelBuf.AddInfoColumn(FORMAT(Text107),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text);

    ExcelBuf.AddInfoColumn(TODAY,FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Date); //Date

    ExcelBuf.AddInfoColumn(TIME,FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Time); //Time

    ExcelBuf.NewRow;

    ExcelBuf.AddInfoColumn(FORMAT(Text108),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text);

    ExcelBuf.AddInfoColumn(FilterString,FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text); //Filterstring

    ExcelBuf.NewRow;

    ExcelBuf.AddInfoColumn(FORMAT(Text109),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text); //Text109 = 'Aged by'

    ExcelBuf.AddInfoColumn(DateTitle,FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text); //Date Title

    ExcelBuf.NewRow;

    ExcelBuf.AddInfoColumn(FORMAT(Text113),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text);

    ExcelBuf.AddInfoColumn(PeriodEndingDate[1],FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Date); //Aging as of Date

    ExcelBuf.NewRow;

    ExcelBuf.AddInfoColumn(FORMAT(Text110),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text); // Text110 = 'Amounts are'

    IF PrintAmountsInLocal THEN

    ExcelBuf.AddInfoColumn(FORMAT(Text112),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text) //Text112 = 'As indicated in Data'

    ELSE

    ExcelBuf.AddInfoColumn(FORMAT(Text111),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf."Cell Type"::Text); //Text111 = 'In our Functional Currency'

    ExcelBuf.ClearNewRow;

    MakeExcelDataHeader;

    Regards

    Rajasekhar.Y

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Aging to Excel Buffer, 'Excel Buffer already exist identification field =1 and value=1

    Sandeep,

    Here is where the error is happening within the debugger.

    SETRANGE("Date Filter",0D,PeriodEndingDate[1]);

    CALCFIELDS("Remaining Amount");

    IF "Remaining Amount" <> 0 THEN   //HAS REMAINING AMT INSERT TO TEMP TABLE

     InsertTemp("Cust. Ledger Entry");

    CurrReport.SKIP;    // this fools the system into thinking that no details "printed"...yet

    Cust. Ledger Entry - OnPostDataItem()

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Aging to Excel Buffer, 'Excel Buffer already exist identification field =1 and value=1

    update pls....

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Aging to Excel Buffer, 'Excel Buffer already exist identification field =1 and value=1

    Hi,

    You should check the below link.

    Excel buffer already exists Error in Export to excel

    dynamicsuser.net/.../295188.aspx

    Excel buffer already exists Error in Export to excel

    www.mibuso.com/.../viewtopic.php;t=37873&start=0

  • mmv Profile Picture
    mmv 11,465 on at
    RE: Aging to Excel Buffer, 'Excel Buffer already exist identification field =1 and value=1

    Hi,

    Try debugging the error, you will come to know at which point the error is triggering.

    BTW why you have called the CreateExcelbook() twice? (as seen from the above code)

  • Verified answer
    Rajasekhar@MS Profile Picture
    Rajasekhar@MS 5,567 on at
    RE: Aging to Excel Buffer, 'Excel Buffer already exist identification field =1 and value=1

    Hi

    MakeExcelInfo Function - can you use the MakeExcelDataHeader on the last line and check

    refer the Aged Accounts Payable report

    Regards

    Rajasekhar.Y

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans