topic Re: Excel sheet in Application Development and Automation Discussions

Excel sheet

Former Member — Tue, 24 Jun 2008 08:20:03 GMT

How to get data from different sheets of an excel file other than using OLE files

Re: Excel sheet

Former Member — Tue, 24 Jun 2008 08:25:49 GMT

hi,

here is the code to deal with multiple Excel Sheet.

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.

Give PTS if Helpful.

Prarthan.

Re: Excel sheet

Former Member — Tue, 24 Jun 2008 08:41:53 GMT

use function module ALSM_EXCEL_TO_INTERNAL_TABLE

Re: Excel sheet

Former Member — Tue, 24 Jun 2008 12:29:19 GMT

Hi,

When we use this function module it will accept the data for only on active sheet of excel file. When I tried to use this, if the cursor is in 1st sheet (active one) then we get data from that sheet only. How to handle other sheet of same excel file.

Regards

Chandralekha

Re: Excel sheet

Former Member — Tue, 24 Jun 2008 12:34:06 GMT

Hi,

Check the following link:

http://www.sap-img.com/abap/uploading-multiple-multitab-excel-sheets-or-ranges.htm

Regards,

Bhaskar

Re: Excel sheet

Former Member — Tue, 24 Jun 2008 13:30:16 GMT

go to the below link:

with luck,

pritam

Re: Excel sheet

Former Member — Wed, 25 Jun 2008 04:53:00 GMT

Thanks Much

Re: Excel sheet

Former Member — Wed, 25 Jun 2008 04:58:06 GMT

Hey,

May i know the solution plz.

Regards,

Naveen