Export the data to multiple sheets in an excell worksheet using Excel BufferTable

Business Central/NAV Developers Forum
1

I wanted to download product category wise sales details in to multiple Excell sheets. Means each category wise details should come in seperate sheets. It would be appreciate if anybody can help me on this requirement.

Thanks,

Jithin .

2

Using Excel Automation as follows

xlWorkBook := xlApp.Workbooks.Add;

xlWorkSheet := xlWorkBook.Worksheets.Add;

xlWorkSheet1 := xlWorkBook.Worksheets.Add;

3

Hi Amol,

It is giving error as shown below…

This message is for C/AL programmers:

Could not invoke the member Visible. The OLE control or Automation server returned an unknown
error code.

Please check the below coding and kindly advise the solution…

Name DataType Subtype Length
xlWorkSheets Automation ‘Microsoft Excel 12.0 Object Library’.Worksheets
xlWorkBooks Automation ‘Microsoft Excel 12.0 Object Library’.Workbooks
xlApp Automation ‘Microsoft Excel 12.0 Object Library’.Application

CREATE(xlApp);
xlApp.Visible(TRUE);
xlApp.Workbooks.Add;
xlWorkBook:=xlApp.ActiveWorkbook;
FOR i:=1 TO 18 DO
xlWorkSheet[i]:=xlWorkBook.Worksheets.Add;
xlWorkSheet[1].Name:=’<=75 kva’;
xlWorkSheet[2].Name:=’>76kva <=375 kva’;
.
.
.
.
FOR i:=1 TO 18 DO
BEGIN
xlWorkSheet[i].Range(‘A’).Value:=‘Customer Code’;
xlWorkSheet[i].Range(‘B’).Value:=‘Name’;
.
.
.
.
.
END;

4

use this command at onpost dataaitem

xlApp.Visible(TRUE);

5

The same error is repeating…

This message is for C/AL programmers:

This Automation variable has not been instantiated.
You can instantiate it by either creating or assigning it.

Code written in onPostDataItem:

IF ISCLEAR(XlApp) THEN
CREATE(XlApp);
XlApp.Visible(TRUE);

XlWrkBk := XlApp.Workbooks.Add;
XlWrkSht := XlWrkBk.Worksheets.Add;
ExcelBuf.CreateSheet(“Excise Prod. Posting Group”,“Excise Prod. Posting Group”,COMPANYNAME(),USERID());
ExcelBuf.DELETEALL(FALSE);

OnAfterGetRecord:

ExcelBuf.NewRow;
ExcelBuf.AddColumn(“Document No.”,FALSE,‘’,FALSE,FALSE,FALSE,‘’);
ExcelBuf.AddColumn(“Posting Date”,FALSE,‘’,FALSE,FALSE,FALSE,‘’);
ExcelBuf.AddColumn(Quantity,FALSE,‘’,FALSE,FALSE,FALSE,‘’);
ExcelBuf.AddColumn(“Unit Price”,FALSE,‘’,FALSE,FALSE,FALSE,‘’);
ExcelBuf.AddColumn(“Line Amount”,FALSE,‘’,FALSE,FALSE,FALSE,‘’);
ExcelBuf.AddColumn(“Excise Amount”,FALSE,‘’,FALSE,FALSE,FALSE,‘’);
ExcelBuf.AddColumn(“eCess Amount”,FALSE,‘’,FALSE,FALSE,FALSE,‘’);
ExcelBuf.AddColumn(“SHE Cess Amount”,FALSE,‘’,FALSE,FALSE,FALSE,‘’);
ExcelBuf.AddColumn(“Amount To Customer”,FALSE,‘’,FALSE,FALSE,FALSE,‘’);

6

Dont move all the code to OnPost Dataitem…

Check whether it compiles without Visible line…

7

Hi Mohana,

It also compiles without visible line

It is compliing up to ExcelBuf.CreateSheet(“Excise Prod. Posting Group”,“Excise Prod. Posting Group”,COMPANYNAME(),USERID());

It is giving error while executing the line XlWrkSht.Name := SheetName; in the function of CreateSheet(SheetName,ReportHeader,CompanyName,UserID2) of table Excel Buffer.

8

Mohana,

Actually the dataitem was indented by another data item. I am sorry to inform you incomplete data.

It is working after I have made some changes as given below:

Sales Invoice Line - OnPostDataItem() :- Indent data item.
i+=1;
IF i=1 THEN
ExcelBuf.CreateBook;
ExcelBuf.CreateSheet(“Excise Prod. Posting Grp”.Code,’’,COMPANYNAME(),USERID());
ExcelBuf.DELETEALL(TRUE);

Now all the sheets are printing properly. But the information / InfoSheed are printing in 2nd sheet onwards. An the last sheet is printed initially just after the Information sheet. I want to print the sheets immediate after the previous one instead of before the previos one. And I want to delete the default shets(Sheet1, Sheet2 & Sheet3). Kindly advise me.

Regards,

Jithin Mathew.

9

Did you try

xlWorkSheet := xlSheets.Item(‘Sheet3’);
xlWorkSheet.Delete;