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