Excel Buffer NAV 2015 - multiple sheets

martinher562
martinher562 Member Posts: 50
edited 2016-12-16 in NAV Three Tier
Hello NAV Experts,

I am having a really difficult time adding an additional sheet to an excel export.
Currently I have the below code on the OnPostReport() trigger and this works fine and I add data to this sheet:

//If export to excel then create excel spreadsheet
IF NOT CreateExcel THEN
CurrReport.BREAK;

ExcelBuffer.CreateBookAndOpenExcel('INFO','Report',COMPANYNAME,USERID) ;


I would like to add a new sheet in the same workbook.

I have tried to follow this: http://www.kopija.in.rs/dynamicsnav/add-multiple-excel-worksheets-dynamics-nav-2013-r2/
and this: https://saurav-nav.blogspot.nl/2015/07/nav-2013-r2-multiple-sheets-in-excel.html

However, I keep getting error messages that variables do not exist and I add one and then just another error message about another variable.


For example: https://dynamicsuser.net/nav/f/beginners/22098/export-to-excel-on-multi-worksheets-using-excel-buffer

This says to add a function to the buffer table 370:
AddSheet()
XlWrkSht := XlWrkBk.Worksheets.Add();

So I add the function and then it says "variable XlWrkSht unknown". So I created it and then I get the same error message but for "XlWrkBk".

On post report trigger I tried adding : ExcelBuffer.AddSheet('NewSheet2') and I get error message that variable AddSheet is unknown.

My question is: Can someone explain how to add a new sheet to the excel export ? Also How to load that sheet with information once is created?
Please explain if I need variables and where the CAL code should be placed.

Thank you all for your time in advance !

Best Answers

Answers

  • archer89
    archer89 Member Posts: 337
    i wrote a posting about that topic.
    follow https://moxie4nav.wordpress.com/2016/07/24/working-with-excel-documents-in-nav/
    best regards
    Franz Kalchmair, MVP
    Alias: Jonathan Archer

    please like / agree / verify my answer, if it was helpful for you. thx.
    Blog: http://moxie4nav.wordpress.com/
  • martinher562
    martinher562 Member Posts: 50
    @archer89 Thank you. Now I am having a problem.

    I was able to create and understand the excel buffer functions better. However, when I follow this tutorial: https://saurav-nav.blogspot.nl/2015/07/nav-2013-r2-multiple-sheets-in-excel.html

    I keep getting the attached error message:
    A DotNet variable has not been instantiated. Attempting to call Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.WorkbookWriter.AddWorksheet in Table Excel Buffer: WriteSheet.
  • martinher562
    martinher562 Member Posts: 50
    @archer89 I was able to create the function, but when I call the function. Nothing happens, not even an error code.
    This is my code:

    OnPostReport()

    //If export to excel then create excel spreadsheet
    IF NOT CreateExcel THEN
    CurrReport.BREAK;

    MakeExcelInfoSheet;

    ExcelBuffer.CreateBookAndOpenExcel('Item','Report',COMPANYNAME,USERID) ;

    ExcelBuffer.AddNewSheet('Data');
  • Kishorm
    Kishorm Member Posts: 921
    You need to call the GiveUserControl() function at the end - this will make the Excel Application visible.
  • martinher562
    martinher562 Member Posts: 50
    @Kishorm Now I get excel to open, only the Item and information tab and not the "Data" tab; which is the tab that the new function is using.

    The function is the code from this page (literally cope and paste): http://www.kopija.in.rs/dynamicsnav/add-multiple-excel-worksheets-dynamics-nav-2013-r2/

    Thank you !

  • martinher562
    martinher562 Member Posts: 50
    @Kishorm You have soo helpful. I finally go it to work. However, the same data gets written into both spreadsheets. I see that in the blog a code unit is created, but I would like to do it in the report triggers.

    Any suggestions on how to get it done ?
    This is the code I used to get more than 1 spreadsheet created.
    OnPostReport()

    ExcelBuffer.CreateBook('Data');
    ExcelBuffer.WriteSheet('Other',COMPANYNAME,USERID);
    ExcelBuffer.AddNewSheet('Test') ;
    ExcelBuffer.CloseBook;
    ExcelBuffer.OpenExcel;
    ExcelBuffer.GiveUserControl;


    @Kishorm again thanks for all the help and sticking with me through this problem.
  • Kishorm
    Kishorm Member Posts: 921
    Have a look at the test codeunit in the blog and follow the same structure, i.e...

    1) For each sheet you want to create populate data and the call AddNewSheet
    2) Do not call the WriteSheet function in your report (this is done in the AddNewSheet function)
    3) At the end CloseBook, OpenExcel & GiveUserControl functions

Categories