Skip to Content
avatar image
Former Member

uploading Multiple Worksheets of single excel sheet

hello,

Is it possible to upload multiple worksheets present in a single excel sheet into internal table.

Regards,

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Sep 29, 2008 at 11:19 AM
    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Function kcd_excel_ole_to_int_convert lets you read 1 (active) worksheet from an excel sheet.

      I copied the source from this function into a FORM routine and made some adjustments to read all available worksheets.

      1. first you need to determine the number of sheets

      2. activate a sheet

      3. import the contents from the activated sheet.

      form kcd_excel_ole_to_int_convert .
      
        data: excel_tab type kcde_sender.
        data: separator type c.
        field-symbols: <field>.
        data: application type ole2_object,
              workbook    type ole2_object,
              range       type ole2_object,
              worksheet   type ole2_object,
              worksheets  type ole2_object,
              sheets      type ole2_object.
        data: h_cell  type ole2_object.
        data: h_cell1 type ole2_object.
      
        data: l_sheet           type c length 40.
        data: l_active_sheet    type i.
      
      
        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.
      
      
      * Create Excel OLE2 object and open XLS 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 workbook 'Open' exporting #1 = pa_file.
        m_message.
      
      
      * Show/don't show XLS
      *  set property of application 'Visible' = 1.
      *  m_message.
      
      
      
      * Determine number of sheets
        call method of application 'Sheets' = sheets.
        m_message.
      
        call method of sheets 'Count' = sheetno.
        m_message.
      
      
      
        l_active_sheet = 0.
        do sheetno times.
      
      
          clear: $i_intern[], excel_tab[].
          clear: it_vals[], it_chars[].
      
          l_active_sheet = l_active_sheet + 1.
      
      *   Activate sheet number L_ACTIVE_SHEET
          call method of application 'Worksheets' = worksheets exporting #1 = l_active_sheet.
          m_message.
      
          call method of worksheets 'Activate'.
          m_message.
      
      
      *   Get active sheet
          get property of  application 'ACTIVESHEET' = worksheet.
          m_message.
      
      *   Find start
          call method of worksheet 'Cells' = h_cell
            exporting #1 = $v_start_row #2 = $v_start_col.
          m_message.
      
      *   Find end
          call method of worksheet 'Cells' = h_cell1
            exporting #1 = $v_end_row #2 = $v_end_col.
          m_message.
      
      *   Create range
          call method of worksheet 'RANGE' = range
            exporting #1 = h_cell #2 = h_cell1.
          m_message.
      
      *   Select range
          call method of range 'SELECT'.
          m_message.
      
      *   copy to Clipboard
          call method of range 'COPY'.
          m_message.
      
      
      
          call function 'CONTROL_FLUSH'
            exceptions
              others = 3.
      
      *   Import clipboard
          call function 'CLPB_IMPORT'
            tables
              data_tab   = excel_tab
            exceptions
              clpb_error = 1
              others     = 2.
          if sy-subrc <> 0. message x001(kx). endif.
      
          separator = cl_abap_char_utilities=>horizontal_tab.
      
          perform separated_to_intern_convert(saplkcde) tables excel_tab $i_intern
                                              using  separator.
      
          set property of application 'CutCopyMode' = 0.
          m_message.
      
      * DO YOUR STUFF ON THE IMPORTED SHEET DATA HERE!
      
        enddo.
      
        call method of application 'QUIT'.
        m_message.
      
        free object : application,
                      workbook,
                      worksheet,
                      sheets,
                      range.
        m_message.
      
      
      endform.                    " KCD_EXCEL_OLE_TO_INT_CONVERT

  • avatar image
    Former Member
    Sep 29, 2008 at 11:17 AM
    Add comment
    10|10000 characters needed characters exceeded