Add new Excel sheet - Dynamics Nav 2016

martynasmatmartynasmat Member Posts: 1
edited 2023-02-09 in NAV Three Tier
I want to add multiple sheets for an Excel export functionality and after hours of searching the internet I have yet to come to a working solution.

I have added 2 new functions to the Excel Buffer table 370
AddNewSheet(SheetName : Text[250])
CurrentRow := 0;
CurrentCol := 0;

XlWrkShtWriter := XlWrkBkWriter.AddWorksheet(SheetName);
IF SheetName <> '' THEN BEGIN
    XlWrkShtWriter.Name := SheetName;
    ActiveSheetName := SheetName;

SetActiveSheet(SheetName : Text[250])
XlWrkShtWriter := XlWrkBkWriter.GetWorksheetByName(SheetName);
IF SheetName <> '' THEN BEGIN
    XlWrkShtWriter.Name := SheetName;
    XlWrkShtWriter.Name := SheetName;

XlWrkSht := XlWrkShtWriter.Worksheet;

I call these functions in a Page
FAMJobsExportExcel; // This function resets the buffer table and adds columns
ExcelBuffer.CreateBook(ExcelFileName, 'testas');
ExcelBuffer.SetActiveSheet('lapastt1', ExcelFileName);
ExcelBuffer.WriteSheet('', COMPANYNAME, USERID);

DocumentsExportExcel; // This function resets the buffer table and adds columns
ExcelBuffer.AddNewSheet('lapastt2'); // This line is probably responsible for the error
ExcelBuffer.SetActiveSheet('lapastt2', ExcelFileName);
ExcelBuffer.WriteSheet('', COMPANYNAME, USERID);

This code throws the error: "A DotNet variable has not been instantiated. Tried calling AddWorksheet with Tabl Excel Buffer: AddNewSheet". Sorry if the translation is inaccurate, the original message was in another language.

After some experimentation I think the error comes up on the 2nd call of the AddNewSheet function - I marked it with a comment. I suspect something in the WriteSheet function inside the Excel Buffer table resets one of the DotNet Excel variables.

I set all the variables to RunOnClient = Yes.


  • AlexDenAlexDen Member Posts: 82

    Perhaps, there is a call of CLEAR(ExcelBuffer) in DocumentsExportExcel function.
    If I'm not mistaken, ExcelBuffer.RESET can also clear variables.

    Try to avoid call of these functions when book is already created, but not finished.
  • krikikriki Member, Moderator Posts: 9,076
    [Topic moved from 'NAV/Navision Classic Client' forum to 'NAV Three Tier' forum]

    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

Sign In or Register to comment.