Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

uploading Multiple Worksheets of single excel sheet

Former Member
0 Kudos

hello,

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

Regards,

1 ACCEPTED SOLUTION

former_member188685
Active Contributor
0 Kudos

Did you check this Blog..

4 REPLIES 4

Former Member

former_member188685
Active Contributor
0 Kudos

Did you check this Blog..

0 Kudos

no solution ppl.

0 Kudos

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