Create new excel worksheet

[Former Member]

Hi all,

Kinldy let me know how to create a new worksheet if the records exceeds 65536 rows in excel.

Im using FM MS_EXCEL_OLE_STANDARD_DAT.

call function 'MS_EXCEL_OLE_STANDARD_DAT'

exporting

file_name = filepath

create_pivot = 0

data_sheet_name = 'Material List'

• pivot_sheet_name = 'Material List1'

• PASSWORD = ' '

• PASSWORD_OPTION = 0

tables

• pivot_field_tab =

data_tab = it_bom_final

fieldnames = out

Am i missing any parameters in the FM or need to add additional logic??

Regards,

Senthil

[Former Member]

Hi all,

Kinldy let me know how to create a new worksheet if the records exceeds 65536 rows in excel.

Im using FM MS_EXCEL_OLE_STANDARD_DAT.

call function 'MS_EXCEL_OLE_STANDARD_DAT'

exporting

file_name = filepath

create_pivot = 0

data_sheet_name = 'Material List'

• pivot_sheet_name = 'Material List1'

• PASSWORD = ' '

• PASSWORD_OPTION = 0

tables

• pivot_field_tab =

data_tab = it_bom_final

fieldnames = out

Am i missing any parameters in the FM or need to add additional logic??

Regards,

Senthil

[Former Member]

Hi Senthil,

Refer sample code for generating multiple Workbook. You can write some logic to split. Hope this helps.

>

• Multiple excel sheets generation in a workbook

CREATE OBJECT EXCEL 'EXCEL.SHEET'.

GET PROPERTY OF EXCEL 'Application' = APPLICATION.

SET PROPERTY OF APPLICATION 'Visible' = 1.

CALL METHOD OF APPLICATION 'Workbooks' = BOOKS.

CALL METHOD OF BOOKS 'Add' = BOOK.

CALL METHOD OF BOOK 'WORKSHEETS' = SHEET.

CALL METHOD OF SHEET 'ADD'.

• Fill all the sheets with relavant data

PERFORM SHEET1 TABLES ITAB1.

PERFORM SHEET2 TABLES ITAB2.

PERFORM SHEET3 TABLES ITAB3.

PERFORM SHEET4 TABLES ITAB4.

• Quit the excel after use

CALL METHOD OF EXCEL 'QUIT'.

FREE OBJECT: COLUMN,SHEET,BOOK,BOOKS,APPLICATION,EXCEL. "NO FLUSH.

CLEAR V_SHEET.

FORM FILL_CELL USING ROW COL VAL.

CALL METHOD OF SHEET 'cells' = CELL NO FLUSH

EXPORTING #1 = ROW #2 = COL.

SET PROPERTY OF CELL 'value' = VAL.

FREE OBJECT CELL NO FLUSH.

ENDFORM. " FILL_CELL

FORM SHEET1 TABLES ITAB1 STRUCTURE ITAB1.

V_SHEET = Sheet Name.

V_NO = V_NO + 1.

CALL METHOD OF BOOK 'worksheets' = SHEET NO FLUSH EXPORTING #1 = V_NO.

SET PROPERTY OF SHEET 'Name' = V_SHEET NO FLUSH.

PERFORM FILL_SHEET1 TABLES ITAB1 USING V_NO V_SHEET.

CALL METHOD OF SHEET 'Columns' = COLUMN.

FREE OBJECT SHEET.

CALL METHOD OF COLUMN 'Autofit'.

FREE OBJECT COLUMN.

ENDFORM.

Repeat above procedure for all sheets you want to add

FORM FILL_SHEET1

TABLES ITAB1 STRUCTURE ITAB1

USING V_NO V_SHEET.

ROW = 1.

PERFORM FILL_CELL USING ROW 1 'Column1 Name'.

PERFORM FILL_CELL USING ROW 2 'Column2 Name'.

PERFORM FILL_CELL USING ROW 3 'Column3 Name'.

ROW = ROW + 1.

LOOP AT ITAB1.

PERFORM FILL_CELL USING ROW 1 ITAB1-Column1.

PERFORM FILL_CELL USING ROW 2 ITAB1-Column2.

PERFORM FILL_CELL USING ROW 3 ITAB1-Column3.

ROW = ROW + 1.

ENDLOOP.

ENDFORM.

Repeat above procedure for all sheets you want to add

Reward points if this Helps.

Manish

[Former Member]

Hi Senthil,

I think that the FM doesn't automatically divide the internal table but you must manage it from program with additional code.

I hope to be you useful.

Regards.

Francesco

[Former Member]

Senthil,

The FM cited does not allow for the creation of multiple Worksheets. You must create multiple Spreadsheets using the FM

or

Use ABAP and OLE automation together to create multiple worksheets within the SAME spreadsheet object.