Reading multiple tabs of excel into an internal table
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
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.
* Error in opening file
ENDIF. Repeat below steps <Number of tabs> times as the excel sheet is going to have max <Number of tabs>
CALL METHOD OF ls_excel ‘Worksheets’ = ls_work
#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
#1 = 2
#2 = 1.
GET PROPERTY OF ls_excel ‘ActiveCell’ = ls_end_cell.
CALL METHOD OF
‘SpecialCells’ = ls_end
#1 = 11.
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’.
data = lt_excel_tab
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.
IF sy–subrc = 0.
“Process internal table data.
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’.