(my version of) Multiple Excel Sheets using Excel Buffer

There have been some earlier posts providing solution on this subject, those are all good and well tested.
However, I would also share another version, which I believe this is much simpler and shorter.

Last time I’m using Excel Buffer was on NAV2009R2 Classic. When working in classic version, I remember that, as long as we use UseInfoShed (… it was wrong, should be UseInfoSheet … haha!), then we can just make as many sheets as we wants.

That’s was old time. It’s not working the same way again now.
UseInfoSheet – not working allowing you producing multiple sheets.
CreateSheet – is no longer exist, and change to;
WriteSheet – this command replaces CreateSheet
AddColumn – has 1 more parameter, CellType
and some more

Let’s start. This is how to customize it –

First, we only need to make 1 function in Table 370 Excel Buffer

The code is:

AddSheet(SheetName : Text[20])

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

We also need only 1 parameter: SheetName – Text[30]

That’s all!

Now, here’s how I’m using it in simple Codeunit, put it in OnRun() section;

xb.DELETEALL(FALSE);

xb.ClearNewRow;
xb.NewRow;
xb.AddColumn ('info1',FALSE,'',FALSE,FALSE,FALSE,'',xb."Cell Type"::Text);
xb.CreateBook('s1');
xb.WriteSheet('','','');
xb.DELETEALL(FALSE);

xb.AddSheet('s2');
xb.ClearNewRow;
xb.NewRow;
xb.AddColumn ('text2',FALSE,'',FALSE,FALSE,FALSE,'',xb."Cell Type"::Text);
xb.WriteSheet('','','');
xb.DELETEALL(FALSE);

xb.AddSheet('s3');
xb.ClearNewRow;
xb.NewRow;
xb.AddColumn ('text3',FALSE,'',FALSE,FALSE,FALSE,'',xb."Cell Type"::Text);
xb.WriteSheet('','','');

xb.DELETEALL(FALSE);

xb.CloseBook;
xb.OpenExcel;
xb.GiveUserControl; MESSAGE('xb.GiveUserControl;');
ERROR('');

When I run it, the result is as below;

Three sheets in one excel workbook.

Please share your thoughts …