how to import data from all excel worksheet ?

[Former Member]

hi

I want to import data from excel worksheet. There is a fm ALSM_EXCEL_TO_INTERNAL_TABLE but it read data only from "actual" sheet. How can I

read data from all exel spread sheets ?

krzys

[Former Member]

hi, may this code be help ful.

TYPE-POOLS: TRUXS.

PARAMETERS: P_FILE TYPE RLGRAP-FILENAME.

TYPES: BEGIN OF T_DATATAB,

COL1(2) TYPE C,

COL2(2) TYPE C,

COL3(6) TYPE C,

COL4(255) TYPE C,

COL5(255) TYPE C,

COL6(255) TYPE C,

COL7(255) TYPE C,

COL8(255) TYPE C,

END OF T_DATATAB.

DATA: IT_DATATAB TYPE STANDARD TABLE OF T_DATATAB,

WA_DATATAB TYPE T_DATATAB.

DATA: IT_RAW TYPE TRUXS_T_TEXT_DATA.

• At selection screen

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

FIELD_NAME = 'P_FILE'

IMPORTING

FILE_NAME = P_FILE.

***********************************************************************

*START-OF-SELECTION.

START-OF-SELECTION.

CLEAR: IT_DATATAB.

REFRESH: IT_DATATAB.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

• I_FIELD_SEPERATOR =

• I_LINE_HEADER = 'X'

I_TAB_RAW_DATA = IT_RAW " WORK TABLE

I_FILENAME = P_FILE

TABLES

I_TAB_CONVERTED_DATA = IT_DATATAB[] "ACTUAL DATA

EXCEPTIONS

CONVERSION_FAILED = 1

OTHERS = 2.

IF SY-SUBRC <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

***********************************************************************

• END-OF-SELECTION.

END-OF-SELECTION.

• SORT IT_DATATAB BY COL1.

• DELETE ADJACENT DUPLICATES FROM IT_DATATAB COMPARING COL1.

LOOP AT IT_DATATAB INTO WA_DATATAB.

NEW-LINE.

WRITE: WA_DATATAB-COL1,

WA_DATATAB-COL2,

WA_DATATAB-COL3,

WA_DATATAB-COL4,

WA_DATATAB-COL5,

WA_DATATAB-COL6,

WA_DATATAB-COL7,

WA_DATATAB-COL8.

ENDLOOP.

reward points if useful.

[Former Member]

Hi,

check this code, this is gathered from one of the thread.

report zole123.

INCLUDE ole2incl.

DATA: count TYPE i,

application TYPE ole2_object,

workbook TYPE ole2_object,

excel TYPE ole2_object,

sheet TYPE ole2_object,

cells TYPE ole2_object.

CONSTANTS: row_max TYPE i VALUE 256.

DATA index TYPE i.

DATA: BEGIN OF itab1 OCCURS 0, first_name(10), END OF itab1.

DATA: BEGIN OF itab2 OCCURS 0, last_name(10), END OF itab2.

DATA: BEGIN OF itab3 OCCURS 0, place(50), END OF itab3.

************************************************************************

*START-OF-SELECTION

START-OF-SELECTION.

APPEND: 'name1' TO itab1, 'surname1' TO itab2,

'worli' TO itab3,

'nam2' TO itab1, 'surname2' TO itab2,

'chowpatty' TO itab3,

'name3' TO itab1, 'surname3' TO itab2,

'versova' TO itab3,

'name4' TO itab1, 'surname4' TO itab2,

'grant road' TO itab3,

'name5' TO itab1, 'surname5' TO itab2,

'gaon' TO itab3,

'name6' TO itab1, 'surname6' TO itab2,

'mahim' TO itab3.

• CREATE OBJECT application 'excel.application'.

• SET PROPERTY OF application 'visible' = 1.

• CALL METHOD OF application 'Workbooks' = workbook.

• CALL METHOD OF workbook 'Add'.

CREATE OBJECT excel 'EXCEL.APPLICATION'.

IF sy-subrc NE 0.

WRITE: / 'No EXCEL creation possible'.

STOP.

ENDIF.

SET PROPERTY OF excel 'DisplayAlerts' = 0.

CALL METHOD OF excel 'WORKBOOKS' = workbook .

SET PROPERTY OF excel 'VISIBLE' = 1.

• Create worksheet

SET PROPERTY OF excel 'SheetsInNewWorkbook' = 1.

CALL METHOD OF workbook 'ADD'.

DO 3 TIMES.

IF sy-index GT 1.

CALL METHOD OF excel 'WORKSHEETS' = sheet.

CALL METHOD OF sheet 'ADD'.

FREE OBJECT sheet.

ENDIF.

ENDDO.

count = 1.

DO 3 TIMES.

CALL METHOD OF excel 'WORKSHEETS' = sheet

EXPORTING

#1 = count.

• perform get_sheet_name using scnt sname.

CASE count.

WHEN '1'.

SET PROPERTY OF sheet 'NAME' = 'firstName'.

CALL METHOD OF sheet 'ACTIVATE'.

" add header here

LOOP AT itab1.

index = row_max * ( sy-tabix - 1 ) + 1. " 1 - column name " for headings change the - 1 to + 1 to accomodate 2 extra lines

CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.

SET PROPERTY OF cells 'Formula' = itab1-first_name.

SET PROPERTY OF cells 'Value' = itab1-first_name.

ENDLOOP.

WHEN '2'.

SET PROPERTY OF sheet 'NAME' = 'LastName'.

CALL METHOD OF sheet 'ACTIVATE'.

" add header here

LOOP AT itab2.

index = row_max * ( sy-tabix - 1 ) + 1. " 1 - column name " for headings change the - 1 to + 1 to accomodate 2 extra lines

CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.

SET PROPERTY OF cells 'Formula' = itab2-last_name.

SET PROPERTY OF cells 'Value' = itab2-last_name.

ENDLOOP.

WHEN '3'.

SET PROPERTY OF sheet 'NAME' = 'place'.

CALL METHOD OF sheet 'ACTIVATE'.

" add header here

LOOP AT itab3.

index = row_max * ( sy-tabix - 1 ) + 1. " 1 - column name " for headings change the - 1 to + 1 to accomodate 2 extra lines

CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.

SET PROPERTY OF cells 'Formula' = itab3-place.

SET PROPERTY OF cells 'Value' = itab3-place.

ENDLOOP.

ENDCASE.

count = count + 1.

ENDDO.

• Save excel speadsheet to particular filename

GET PROPERTY OF excel 'ActiveSheet' = sheet.

CALL METHOD OF sheet 'SaveAs'

EXPORTING #1 = 'c:\temp\exceldoc1.xls' "filename

#2 = 1. "fileFormat

Note: to make headings, change the -1 to +1 where specified in the above code and add the following where i have mentioned to add it

index = row_max * ( sy-tabix - 1 ) + 1.

CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.

SET PROPERTY OF cells 'value' = header1.

Reward for helpful answers

Thanks

Naveen khan