Add new Excel sheet - Dynamics Nav 2016

martynasmat

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;
END;


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

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.AddNewSheet('lapastt1');
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.

AlexDen

Hello,

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.

kriki

[Topic moved from 'NAV/Navision Classic Client' forum to 'NAV Three Tier' forum]