Skip to Content
author's profile photo Former Member
Former Member

how to import data from all excel worksheet ?

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Sep 20, 2007 at 07:44 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 20, 2007 at 09:37 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.