Skip to Content
Author's profile photo Former Member

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’
        #1          = p_file.

    IF sysubrc NE 0.
*     Error in opening file

Repeat below steps <Number of tabs> times as the excel sheet is going to have max <Number of 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
          #1           = syindex.

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
          #1       = 2
          #2       = 1.

Getting the last cell that is filled with data

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

Now Create range with starting cell and ending cell

      CALL METHOD OF  ls_excel   ‘Range’  = ls_range
          #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
          data                 = lt_excel_tab
          cntl_error           = 1
          error_no_gui         = 2
          not_supported_by_gui = 3
          OTHERS               = 4.
      IF sysubrc = 0.
        “Process internal table data.

      REFRESH lt_excel_tab.


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’.

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.