Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / Converting Nav 4.0 rep...
Microsoft Dynamics NAV (Archived)

Converting Nav 4.0 report to excel to NAV 2013 R2- Using Excel Buffer

(0) ShareShare
ReportReport
Posted on by Michael Mo
Firstly I am not a developer or programmer... We are currently upgrading to Nav 2013, and I want to update my bespoke report to Nav 2013 r2. The report opens an excel template which used to be stored on the users pc (good for security limiting users that can run this). Now it appears as though I have to let the server to run the report. My question is... if the code was ... ExcelBuf.OpenBook(ExcelFileName, 'Data'); ExcelBuf.CreateSheet('Data','xxx',COMPANYNAME,USERID); ExcelBuf.GiveUserControl; And the CreateSheet changes to WriteSheet ExcelBuf.OpenBook(ExcelFileName, 'Data'); ExcelBuf.WriteSheet (PADSTR(STRSUBSTNO('%1 %2','Data','xxx'),30), COMPANYNAME, USERID); ExcelBuf.GiveUserControl; I then find that on the nav server that the file format is not allowed to be xlt, so changed to xlxs. Final straw is "A call to Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.WorkbookReader.Open failed with the message: \\servername\folder\filename.xlxs" As I say I am no developer, but having made a perfectly good report I just want to upgrade it. Any help appreciated.

*This post is locked for comments

  • LuisT Profile Picture
    LuisT 55 on at
    RE: Converting Nav 4.0 report to excel to NAV 2013 R2- Using Excel Buffer

    Ok, thanks for such a fast answer. I will try to update the plattform and check it is solved.

  • Suggested answer
    4BzSoftware Profile Picture
    4BzSoftware 6,071 on at
    RE: Converting Nav 4.0 report to excel to NAV 2013 R2- Using Excel Buffer

    Hi Luis,

    I faced this problem when I developed Query and Excel Report tool.

    It is due to bug, Platform Hotfix solved this issue:

  • LuisT Profile Picture
    LuisT 55 on at
    RE: Converting Nav 4.0 report to excel to NAV 2013 R2- Using Excel Buffer

    Hello Michael,

    I am getting exactly the last error you reported in the blog: " A call to Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.WorkbookWriter.Open failed with this message: The custom numbering ID format should start at 164 and should be listed in consecutive order.

    Could you explain or give me a more detailed descriptions of the changes you made when you refered to: "I have added the format to the entercell function, and added the number format, and every thing work well". What change did you do to the function?

    Thanks in advance. Neither I am a developer ;)

  • Verified answer
    Michael Mo Profile Picture
    Michael Mo on at
    RE: Converting Nav 4.0 report to excel to NAV 2013 R2- Using Excel Buffer

    Ok now I get it!  I have added the format to the entercell function, and added the number format, and every thing work well.

    The issue was in the original excel template some of the cells were formatted to show "as thousands" ie 1,000, now the entercell function takes care of this.

    Once again many thanks. I now have an Nav 2013 working excel cost sheet to keep my users happy.

    Let's hope the actual system works as well as the report when we go live !

  • Michael Mo Profile Picture
    Michael Mo on at
    RE: Converting Nav 4.0 report to excel to NAV 2013 R2- Using Excel Buffer

    Hi Daniele, firstly many thanks, after some "trial and error" testing, I can now update workbooks, the code is working. So many thanks.

    One last design issue compared to 4.0 is.....the report does not seems to like the fact that my existing file has text and cell formatting. If I run the report into a blank saved file, the report work fine, otherwise it throws an error " A call to Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.WorkbookWriter.Open failed with this message: The custom numbering ID format should start at 164 and should be listed in consecutive order.

    Any ideas on this would be helpful.

    Thanks

    Michael

    ps. Still not a developer!

  • Suggested answer
    Daniele Rebussi Profile Picture
    Daniele Rebussi 2,480 on at
    RE: Converting Nav 4.0 report to excel to NAV 2013 R2- Using Excel Buffer

    Hi, for update an existing file you can refer to report 29 "Export Acc. Sched. to Excel", when running "Update Workbook" option.

    Regarding the equation, you need to enter it in Formula field of Excel Buffer table, using a numeric number format (r.g. "'#,##0.00"): since it contains some SUM and IF statements, are you using Excel in English to make it properly been parsed?

  • Michael Mo Profile Picture
    Michael Mo on at
    RE: Converting Nav 4.0 report to excel to NAV 2013 R2- Using Excel Buffer

    Hi Suarav

    firstly thank you for the reply. I have now got the excel file producing, but....  we had a template file we opened, and we filled it with equations such as

    =IF(D19="CHF",sum(11671*$J$5),IF(D19="USD",sum(11671*$J$6),IF(D19="EUR",sum(11671*$J$7),IF(D19="LCY",11671))))

    The excel buffer doesn't seem to like this and puts it in as text. I looked at the code of table 370 (Excel Buffer) and the options don't seem to allow for this.

    So in summary there are two issues:

    1. I can't find a function to open an existing template.

    2. Our complex formulas do not convert when opening the excel file.

    Any comments appreciated.

  • Saurav.Dhyani Profile Picture
    Saurav.Dhyani 17,965 Super User 2024 Season 2 on at
    RE: Converting Nav 4.0 report to excel to NAV 2013 R2- Using Excel Buffer

    Dear Michael.

    Please go through the link below -

    saurav-nav.blogspot.in/.../nav-2013-nav-2013-r2-save-export-to.html

    and let me know if any issues.

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,253 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,188 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans