Export to Excel on multi-worksheets using the Excel Buffer

Prashanthbluestar

Hi All,

I want to export the data to an excel sheet,over different worksheets using Excel buffer or any other method(If any).

I am able to get the data over a single worksheet but i want the data to spread across multiple worksheets.So,How can i achieve this....

Is there any function capable of doing the above.... please help me out with this.... ](*,)

Regards,
Prashanth Sanath

McClane

Creating a new sheet in an existing book can be done like this:

clear(ExcelApp);
Create(ExcelApp);
ExcelBook:=ExcelApp.Workbooks.Add();
ExcelSheet:=ExcelApp.ActiveSheet;
ExcelSheet.Name:='Name1';
ExcelSheet.Range('A1').Value:='Headline1';
// 2nd Sheet
ExcelSheet:=ExcelBook.Sheets.add();
ExcelSheet:=ExcelApp.ActiveSheet;
ExcelSheet.Name:='Name2';
ExcelSheet.Range('A1').Value:='Headline2';

Variables are:
ExcelApp: Automation 'Microsoft Excel 11.0 Object Library'.Application
ExcelBook: Automation 'Microsoft Excel 11.0 Object Library'.Workbook
ExcelSheet : Automation 'Microsoft Excel 11.0 Object Library'.Worksheet

Variables of these types ared use in the table Excel Buffer, so you can add a function there or create the Excelbook by using your own code

ara3n

it's not possible with buffer, you have to take make modification.

Even if you call the create sheet with different parameters, navision buffer excel creates one sheet with last sheetname

Prashanthbluestar

ara3n wrote:

it's not possible with buffer, you have to take make modification.

Even if you call the create sheet with different parameters, navision buffer excel creates one sheet with last sheetname

Thanx for the reply.

Well if u tell that it's not possible using the EXCEL BUFFER then is it possible through anyother method?

ara3n

Yes, modify the excel buffer and add the functionality for mutiple worksheet.

McClane

The code you want is above \:D/

Hanen

Hello,

I want to get an Excel workbook with 3 sheets but I am using the ExcelBuffer table and a report to export my data.
I don't know how to mak it work

Anyone can help me???? :oops:

Belias

you can't use excel buffer "as is" for multi sheets export (as you probably have read in previous posts).
You can modify the excel buffer table adding a new field with sheet name and then implement one or more functions to redirect the export (i would NOT go for this, anyway)
OR:
you can search mibuso for the instructions to write to multiple sheets. (e.g. McClane code you can read above)

I don't recommend you to modify the excel buffer because you'll have to upgrade it every release and also the code is a little hard to read (in my opinion), so it will take a lot of time and tests to have it working. Remember that the buffer is also used from other standard processes, and you don't want them to fail, isn't it?
And i just remember that the key of the table is "Row no.", "Col no."...with multiple sheets, you have to extend the key with the new "worksheet no." filed, too!