Upload multy sheet excel into internal table

ABAP - Upload multy sheet excel into internal table
Salam ABAP HR Indonesia !

Suatu saat ada kebutuhan upload dari excel ke sap nah berhubung pakai function ALSM_EXCEL_TO_INTERNAL_TABLE ada keterbatasan yaitu hanya bisa baca sheet yang active sementara kebutuhannya pengen baca fleksibel sheet lebih dari satu. nah akhirnya aye copy function ALSM_EXCEL_TO_INTERNAL_TABLE itu ke ZALSM_EXCEL_SHEET_TO_ITAB terus di edit dikit. edit nya apa aja ada di bawah ini yuk kita simak.


1. tambah parameter zsheetnm di tab import.


2. edit source nya jadi kayak dibawah ini

FUNCTION ZALSM_EXCEL_SHEET_TO_ITAB.
*"----------------------------------------------------------------------
*"*"Local interface:
*"  IMPORTING
*"     VALUE(FILENAME) LIKE  RLGRAP-FILENAME
*"     VALUE(I_BEGIN_COL) TYPE  I
*"     VALUE(I_BEGIN_ROW) TYPE  I
*"     VALUE(I_END_COL) TYPE  I
*"     VALUE(I_END_ROW) TYPE  I
*"     REFERENCE(ZSHEETNAME) TYPE  ZSHEETNM
*"  TABLES
*"      INTERN STRUCTURE  ALSMEX_TABLINE
*"  EXCEPTIONS
*"      INCONSISTENT_PARAMETERS
*"      UPLOAD_OLE
*"----------------------------------------------------------------------

DATA: EXCEL_TAB     TYPE  TY_T_SENDER.
DATA: LD_SEPARATOR  TYPE  C.
FIELD-SYMBOLS: <FIELD>.
DATA: APPLICATION   TYPE  OLE2_OBJECT,
WORKBOOK      TYPE  OLE2_OBJECT,
RANGE         TYPE  OLE2_OBJECT,
WORKSHEET     TYPE  OLE2_OBJECT,
************************************************************
* added to read dedicated sheet
************************************************************
SHEETS        TYPE  OLE2_OBJECT.
************************************************************
* added to read dedicated sheet
************************************************************
DATA: H_CELL        TYPE  OLE2_OBJECT,
H_CELL1       TYPE  OLE2_OBJECT.

* Makro für Fehlerbehandlung der Methods
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.


* check parameters
IF I_BEGIN_ROW > I_END_ROW. RAISE INCONSISTENT_PARAMETERS. ENDIF.
IF I_BEGIN_COL > I_END_COL. RAISE INCONSISTENT_PARAMETERS. ENDIF.

* set seperator. Direct move is not sufficient (cause of hex type)
ASSIGN LD_SEPARATOR TO <FIELD> TYPE 'X'.
<FIELD> = GC_HEX_TAB.

* open file in Excel
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 WORKBOOK 'Open'    EXPORTING #1 = FILENAME.
M_MESSAGE.
*  set property of application 'Visible' = 1.
*  m_message.

************************************************************
* added to read dedicated sheet
************************************************************
IF ZSHEETNAME NE ''.
* Determine number of sheets
CALL METHOD OF APPLICATION 'Sheets' = SHEETS.
M_MESSAGE.

*    CALL METHOD OF SHEETS 'Count' = SHEETNO.
*    M_MESSAGE.

* Activate sheet number L_ACTIVE_SHEET with tab name
CALL METHOD OF APPLICATION 'Worksheets' = WORKSHEET
EXPORTING #1 = ZSHEETNAME.
M_MESSAGE.

CALL METHOD OF WORKSHEET 'Activate'.
M_MESSAGE.

*    GET PROPERTY OF worksheets 'Name' = ZSHEETNAME.
ENDIF.
************************************************************
* added to read dedicated sheet
************************************************************

GET PROPERTY OF  APPLICATION 'ACTIVESHEET' = WORKSHEET.
M_MESSAGE.

* mark whole spread sheet
CALL METHOD OF WORKSHEET 'Cells' = H_CELL
EXPORTING #1 = I_BEGIN_ROW #2 = I_BEGIN_COL.
M_MESSAGE.
CALL METHOD OF WORKSHEET 'Cells' = H_CELL1
EXPORTING #1 = I_END_ROW #2 = I_END_COL.
M_MESSAGE.

CALL METHOD  OF WORKSHEET 'RANGE' = RANGE
EXPORTING #1 = H_CELL #2 = H_CELL1.
M_MESSAGE.
CALL METHOD OF RANGE 'SELECT'.
M_MESSAGE.

* copy marked area (whole spread sheet) into Clippboard
CALL METHOD OF RANGE 'COPY'.
M_MESSAGE.

* Without control flush, CLPB_IMPORT does not find any data
CALL FUNCTION 'CONTROL_FLUSH'
EXCEPTIONS
OTHERS = 3.

* read clipboard into ABAP
CALL FUNCTION 'CLPB_IMPORT'
TABLES
DATA_TAB   = EXCEL_TAB
EXCEPTIONS
CLPB_ERROR = 1
OTHERS     = 2.
IF SY-SUBRC <> 0.
MESSAGE A037(ALSMEX).
ENDIF.

PERFORM SEPARATED_TO_INTERN_CONVERT TABLES EXCEL_TAB INTERN
USING  LD_SEPARATOR.

* clear clipboard
REFRESH EXCEL_TAB.
CALL FUNCTION 'CLPB_EXPORT'
TABLES
DATA_TAB   = EXCEL_TAB
EXCEPTIONS
CLPB_ERROR = 1
OTHERS     = 2.

* quit Excel and free ABAP Object - unfortunately, this does not kill
* the Excel process
CALL METHOD OF APPLICATION 'QUIT'.
M_MESSAGE.

FREE   OBJECT APPLICATION.
M_MESSAGE.

* >>>>> Begin of change note 575877
* to kill the Excel process it's necessary to free all used objects
FREE OBJECT H_CELL.       M_MESSAGE.
FREE OBJECT H_CELL1.      M_MESSAGE.
FREE OBJECT RANGE.        M_MESSAGE.
FREE OBJECT WORKSHEET.    M_MESSAGE.
FREE OBJECT WORKBOOK.     M_MESSAGE.
*  FREE OBJECT application.  m_message.
* >>>>>> End of change note 575877
ENDFUNCTION.

3. nah tinggal di pake deh functionnya, contohnya :

* --Load Excel actual sheet
REFRESH ITDATA.
CALL FUNCTION 'ZALSM_EXCEL_SHEET_TO_ITAB'
EXPORTING
FILENAME    = ZFILESRC
I_BEGIN_COL = BCOL
I_BEGIN_ROW = BROW
I_END_COL   = ECOL
I_END_ROW   = EROW
ZSHEETNAME  = 'Actual'
TABLES
INTERN      = ITDATA.

nah kalo mau liat di web lainnya bisa liat di sini nih karena aye dapet idenya dari situ : https://www.sdn.sap.com/irj/scn/thread?tstart=0&threadID=1272542&messageID=7207436

selamat mencoba yeeee.