Skip to Content
avatar image
Former Member

How to Upload several Sheet with function ALSM_EXCEL_TO_INTERNAL_TABLE?

I try to upload Excel sheet with function ALSM_EXCEL_TO_INTERNAL_TABLE. It

run good but only one sheet(active sheet) is available for upload.

How can i upload other sheet.

for example:My Excel file has 3 sheets(may be more): P1, P2 and P3.

When i save the file with P1 active, P1 is uploaded, when i save with P2 active, P2 is Uploaded.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    avatar image
    Former Member
    Sep 08, 2008 at 06:21 AM

    you can not use FM to upload multiple sheets, you have to use OLE to upload multiple sheets in SAP.

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 08, 2008 at 06:26 AM

    Hello,

    ALSM_EXCEL_TO_INTERNAL_TABLE doesn't work with multiple sheets at once.

    You can use the following OLE code to achieve that. The program below works for 2 sheets which can be modified to work with more than that as well

    *&---------------------------------------------------------------------*
    *& Report  ZKRIS_EXCELUPLOAD_2SHEETS
    *&
    *&---------------------------------------------------------------------*
    *&
    *&
    *&---------------------------------------------------------------------*
    
    REPORT  zkris_excelupload_2sheets.
    
    
    *      value of excel-cell
    TYPES: ty_d_itabvalue             TYPE alsmex_tabline-value,
    *      internal table containing the excel data
           ty_t_itab                  TYPE alsmex_tabline   OCCURS 0,
    
    *      line type of sender table
           BEGIN OF ty_s_senderline,
             line(4096)               TYPE c,
           END OF ty_s_senderline,
    *      sender table
           ty_t_sender                TYPE ty_s_senderline  OCCURS 0.
    
    *
    
    CONSTANTS:  gc_esc              VALUE '"'.
    INCLUDE lalsmexf01.
    TYPE-POOLS ole2.
    
    START-OF-SELECTION.
    
      PARAMETERS: filename LIKE rlgrap-filename.
      PARAMETERS: st_rw_s1 TYPE i.
      PARAMETERS: st_cl_s1 TYPE i.
      PARAMETERS: st_rw_s2 TYPE i.
      PARAMETERS: st_cl_s2 TYPE i.
      PARAMETERS: ed_rw_s1 TYPE i.
      PARAMETERS: ed_cl_s1 TYPE i.
      PARAMETERS: ed_rw_s2 TYPE i.
      PARAMETERS: ed_cl_s2 TYPE i.
    
      DATA: it_data1 TYPE ty_t_itab.
      DATA: it_data2 TYPE ty_t_itab.
    
      DATA: it_data_wa LIKE LINE OF it_data1.
    
    
    
    
    
    * DATA DECLARATION
    
      DATA: excel_tab TYPE ty_t_sender,
      excel_tab1 TYPE ty_t_sender.
    
      DATA: ld_separator TYPE c.
      DATA: application TYPE ole2_object,
      workbook TYPE ole2_object,
      sheet TYPE ole2_object,
      range TYPE ole2_object,
      worksheet TYPE ole2_object.
      DATA: h_cell TYPE ole2_object,
      h_cell1 TYPE ole2_object.
      DATA: ld_rc TYPE i.
    
    * MESSAGE DEFINATION
    
      DEFINE m_message.
        case sy-subrc.
          when 0.
          when 1.
            message id sy-msgid type sy-msgty number sy-msgno
            with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
          when others. raise upload_ole.
        endcase.
      END-OF-DEFINITION.
    
    
    * PARAMETER CHECK
    
      IF st_rw_s1 > ed_rw_s1.
        RAISE inconsistent_parameters.
      ENDIF.
    
      IF st_cl_s1 > ed_cl_s1.
        RAISE inconsistent_parameters.
      ENDIF.
    
      IF st_rw_s2 > ed_rw_s2.
        RAISE inconsistent_parameters.
      ENDIF.
    
      IF st_cl_s2 > ed_cl_s2.
        RAISE inconsistent_parameters.
      ENDIF.
    
    
      CLASS cl_abap_char_utilities DEFINITION LOAD.
      ld_separator = cl_abap_char_utilities=>horizontal_tab.
    
    * OPENING EXCEL FILE
      IF application-header = space OR application-handle = -1.
        CREATE OBJECT application 'Excel.Application'.
        m_message.
      ENDIF.
      CALL METHOD OF application 'Workbooks' = workbook.
      m_message.
      CALL METHOD OF application 'Workbooks' = workbook.
      m_message.
      CALL METHOD OF workbook 'Open'
        EXPORTING
        #1 = filename.
      m_message.
      CALL METHOD OF application 'Worksheets' = sheet
        EXPORTING
        #1 = 1.
      m_message.
      CALL METHOD OF application 'Worksheets' = sheet
        EXPORTING
        #1 = 1.
      m_message.
      CALL METHOD OF sheet 'Activate'.
      m_message.
      GET PROPERTY OF application 'ACTIVESHEET' = sheet.
      m_message.
    
    * MARKING OF WHOLE SPREADSHEET
      CALL METHOD OF sheet 'Cells' = h_cell
        EXPORTING
        #1 = st_rw_s1
        #2 = st_cl_s1.
      m_message.
      CALL METHOD OF sheet 'Cells' = h_cell1
        EXPORTING
        #1 = ed_rw_s1
        #2 = ed_cl_s1.
      m_message.
    
      CALL METHOD OF sheet 'RANGE' = range
        EXPORTING
        #1 = h_cell
        #2 = h_cell1.
      m_message.
      CALL METHOD OF range 'SELECT'.
      m_message.
    
    * Copy marked area (SHEET1) into Clippboard
    
      CALL METHOD OF range 'COPY'.
      m_message.
    
    * Read clipboard into ABAP
    
      CALL METHOD cl_gui_frontend_services=>clipboard_import
      IMPORTING
      data = excel_tab
      EXCEPTIONS
      cntl_error = 1
    * ERROR_NO_GUI = 2
    * NOT_SUPPORTED_BY_GUI = 3
      OTHERS = 4
      .
      IF sy-subrc <> 0.
        MESSAGE a037(alsmex).
      ENDIF.
    
      PERFORM separated_to_intern_convert TABLES excel_tab it_data1
      USING ld_separator.
    
    * Clear the clipboard
    
      REFRESH excel_tab.
      CALL METHOD cl_gui_frontend_services=>clipboard_export
      IMPORTING
      data = excel_tab
      CHANGING
      rc = ld_rc
      EXCEPTIONS
      cntl_error = 1
    * ERROR_NO_GUI = 2
    * NOT_SUPPORTED_BY_GUI = 3
      OTHERS = 4
      .
    
    * Working in Second Excel Work Sheet
    
      CALL METHOD OF application 'Worksheets' = sheet
        EXPORTING
        #1 = 2.
      m_message.
      CALL METHOD OF sheet 'Activate'.
      m_message.
      GET PROPERTY OF application 'ACTIVESHEET' = sheet.
      m_message.
    * Mark Sheet2
    
      CALL METHOD OF sheet 'Cells' = h_cell
        EXPORTING
        #1 = st_rw_s2
        #2 = st_cl_s2.
      m_message.
      CALL METHOD OF sheet 'Cells' = h_cell1
        EXPORTING
        #1 = ed_rw_s2
        #2 = ed_cl_s2.
      m_message.
    
      CALL METHOD OF sheet 'RANGE' = range
        EXPORTING
        #1 = h_cell
        #2 = h_cell1.
      m_message.
      CALL METHOD OF range 'SELECT'.
      m_message.
    
    * Copy Marked Area (Sheet2) into Clippboard
    
      CALL METHOD OF range 'COPY'.
      m_message.
    
    * Read Clipboard into ABAP
    
      CALL METHOD cl_gui_frontend_services=>clipboard_import
      IMPORTING
      data = excel_tab1
      EXCEPTIONS
      cntl_error = 1
    * ERROR_NO_GUI = 2
    * NOT_SUPPORTED_BY_GUI = 3
      OTHERS = 4
      .
      IF sy-subrc <> 0.
        MESSAGE a037(alsmex).
      ENDIF.
    
      PERFORM separated_to_intern_convert TABLES excel_tab1 it_data2
      USING ld_separator.
    
    * Clear Clipboard
    
      REFRESH excel_tab.
      CALL METHOD cl_gui_frontend_services=>clipboard_export
      IMPORTING
      data = excel_tab1
      CHANGING
      rc = ld_rc
      EXCEPTIONS
      cntl_error = 1
    * ERROR_NO_GUI = 2
    * NOT_SUPPORTED_BY_GUI = 3
      OTHERS = 4
    * Leaving Application
      .
      CALL METHOD OF application 'QUIT'.
      m_message.
    
      FREE OBJECT application.
      m_message.
    
    
      LOOP AT it_data1 INTO it_data_wa.
        WRITE:/ it_data_wa.
      ENDLOOP.
    
      SKIP 3.
    
      LOOP AT it_data2 INTO it_data_wa.
        WRITE:/ it_data_wa.
      ENDLOOP.
    

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 08, 2008 at 06:29 AM
    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 26, 2008 at 08:50 AM

    Thanks for Good Source Code to import excel.

    While Applying suggested code,

    I got a exception at the call of 'clipboard_import' method of cl_gui_frontend_services "from time to time".

    The Exception is "cntl_error".

    I wonder what's the cause of this exception and why it raises exception randomly.

    (with same excel file and same session, some times it works but some times it raises exception.

    and there is still data in clipboard)

    Any Idea would be great if you could help me with this!

    Thanks and regards,

    Younho.

    ============================================

    • Read Clipboard into ABAP

    CALL METHOD cl_gui_frontend_services=>clipboard_import

    IMPORTING

    data = excel_tab1

    EXCEPTIONS

    cntl_error = 1

    • ERROR_NO_GUI = 2

    • NOT_SUPPORTED_BY_GUI = 3

    OTHERS = 4

    .

    IF sy-subrc 0.

    MESSAGE a037(alsmex).

    ENDIF.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 28, 2010 at 08:03 AM

    has solved

    Add comment
    10|10000 characters needed characters exceeded