Excel Buffer NAV 2015 - multiple sheets

martinher562

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 !

archer89

seems that variable xlwrkbkwriter (type ...WorkbookWriter) is not created.
maybe this code sample is helpful for you: http://www.kopija.in.rs/dynamicsnav/add-multiple-excel-worksheets-dynamics-nav-2013-r2/

Kishorm

Have a read through the blog again, it says...

Also, there is a difference that we cannot use standard function CreateBookAndOpenExcel – because it is used for single worksheet case scenario – but we must “close” Excel manually by calling standard functions CloseBook, OpenExcel, GiveUserControl on Excel Buffer.

Look at the code in the OnRun() trigger of the test codeunit - you need to use that instead.

archer89

i wrote a posting about that topic.
follow https://moxie4nav.wordpress.com/2016/07/24/working-with-excel-documents-in-nav/

martinher562

@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.

archer89

seems that variable xlwrkbkwriter (type ...WorkbookWriter) is not created.
maybe this code sample is helpful for you: http://www.kopija.in.rs/dynamicsnav/add-multiple-excel-worksheets-dynamics-nav-2013-r2/

martinher562

@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

You need to call the GiveUserControl() function at the end - this will make the Excel Application visible.

martinher562

@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 !

Kishorm

Have a read through the blog again, it says...

Also, there is a difference that we cannot use standard function CreateBookAndOpenExcel – because it is used for single worksheet case scenario – but we must “close” Excel manually by calling standard functions CloseBook, OpenExcel, GiveUserControl on Excel Buffer.

Look at the code in the OnRun() trigger of the test codeunit - you need to use that instead.

martinher562

@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

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