Reading multiple tabs of excel into an internal table

Reading multiple tabs of excel into an internal table.

Steps given below in the document describes reading multiple tabs of an excel file into an internal table using OLE commands.

DATA:lt_excel_tab    TYPE STANDARD TABLE OF char2000,“Data in Excel
          ls_excel_tab         TYPE char2000,       “Data in Excel
          ls_work                 TYPE ole2_object,    “object for worksheet
          ls_start_cell          TYPE ole2_object,    “Object for starting cell
          ls_excel                TYPE ole2_object,    “Object for application
          ls_workbook         TYPE ole2_object,    “Object for workbook
          ls_end_cell           TYPE ole2_object,    “object for end of cell
          ls_end                  TYPE ole2_object,   “Ending cell
          ls_range               TYPE ole2_object,    “Object for Range
          l_text                    TYPE string,             “Extra fields in the line
          ln_off                    TYPE i.                    “Worksheet number

Create Object reference for Excel Application.

CREATE OBJECT ls_excel ‘Excel.Application’.

Get Property of the workbooks by calling WORKBOOKS method of excel application.

CALL METHOD OF ls_excel ‘WORKBOOKS’ =ls_workbook.

Call method OPEN of workbook to open workbook exporting file path.

    CALL METHOD OF   ls_workbook    ‘Open’
      EXPORTING
        #1          = p_file.

    IF sy–subrc NE 0.
*     Error in opening file
    ENDIF.

Repeat below steps <Number of tabs> times as the excel sheet is going to have max <Number of tabs>
tabs.

DO <Number of tabs> TIMES.

Pass index number of excel sheet tab. If index number is 1 then first tab will be opened to read.

      CALL METHOD OF ls_excel ‘Worksheets’ = ls_work
        EXPORTING
          #1           = sy–index.

If the sheet is available then activate the sheet.
CALL METHOD OF ls_work ‘Activate’.

If you want to set starting row and column for reading data then call below method of excel application. Here starting cell is set as 2nd row and 1st column.
      CALL METHOD OF ls_excel ‘Cells’ = ls_start_cell
        EXPORTING
          #1       = 2
          #2       = 1.

Getting the last cell that is filled with data

      GET PROPERTY OF ls_excel ‘ActiveCell’ = ls_end_cell.
      CALL METHOD OF
          ls_end_cell
          ‘SpecialCells’ = ls_end
        EXPORTING
          #1             = 11.

Now Create range with starting cell and ending cell

      CALL METHOD OF ls_excel   ‘Range’ = ls_range
        EXPORTING
          #1       = ls_start_cell
          #2       = ls_end.

* Selecting the Range
CALL METHOD OF ls_range ‘Select’.

* Copying the range
CALL METHOD OF ls_range ‘Copy’.

REFRESH: lt_excel_tab[].

Now call method cl_gui_frontend_services=>clipboard_import to read clipboard into internal table.

      CALL METHOD cl_gui_frontend_services=>clipboard_import
        IMPORTING
          data                 = lt_excel_tab
        EXCEPTIONS
          cntl_error           = 1
          error_no_gui         = 2
          not_supported_by_gui = 3
          OTHERS               = 4.
      IF sy–subrc = 0.
        “Process internal table data.
      ENDIF.

REFRESH lt_excel_tab.

ENDDO.

Skip message window displayed before closing the application
SET PROPERTY OF ls_excel ‘DisplayAlerts’ = 0.

Call method QUIT to Close the file.

CALL METHOD OF ls_excel ‘Quit’.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Reading multiple tabs of excel into an internal table

Assigned Tags