excel ole

[Former Member]

how can i read data from the second work-sheet of an excel?

[Former Member]

Hi,

Check this..., this will help .

regards,

madhuri

[former_member189059]

the above post shows a function module which has to be put in a package

in most cases, we cannot add to that package

using that code, i made a standalone program...

*&---------------------------------------------------------------------*
*& 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.