Issue in excel upload with ALSM Function module
Hi Experts,
As per my requirement I need to upload the more then 10k records from different sheets from same Excel file. But standard ALSM_EXCEL_TO_INTERNAL_TABLE FM end row length( i_end_row-NUMC4) 9999 records only. For that reason I copied the standard ALSM function module to ZALSM as custom FM now I increased the End row length(NUMC-5). Accordingly I made the some changes to custom FM for reading the different sheets. So the now I am able to upload the more than 10K records. But the problem is some times while uploading the excel file through my custom ZALSM FM. it is not reading single record also from the excel attachment. I observe Inside FM zalsm there is method(highlighted bold ) :cl_gui_frontend_services=>clipboard_import-> This method some times failing to read the records from excel. So could you please help on this. How to overcome this problem.
Imp Note: If I restarted my desktop and then i tried to upload the excel and in that case it is reading the all records from excel by this method cl_gui_frontend_services=>clipboard_import. Every time restart the desktop is not good. so please help on this to overcome the problem.
*"*"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(SHEETS) TYPE I
*" TABLES
*" IT_DATA STRUCTURE ZALSMEX_TABLINE
*" EXCEPTIONS
*" INCONSISTENT_PARAMETERS
*" UPLOAD_OLE
*"------------------------------------------------------------------
DATA: excel_tab TYPE ty_t_sender.
DATA: ld_separator TYPE c.
DATA: application TYPE ole2_object,
workbook TYPE ole2_object,
range TYPE ole2_object,
worksheet TYPE ole2_object.
DATA: h_cell TYPE ole2_object,
h_cell1 TYPE ole2_object.
DATA:
ld_rc TYPE i.
* Rückgabewert der Methode "clipboard_export "
* 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.
* Get TAB-sign for separation of fields
CLASS cl_abap_char_utilities DEFINITION LOAD.
ld_separator = cl_abap_char_utilities=>horizontal_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.
* GET PROPERTY OF application 'ACTIVESHEET' = worksheet.
* m_message.
DATA sheetno TYPE i.
DATA : sheetno_temp TYPE i .
* CLEAR sheetno_temp.
DO sheets TIMES.
sheetno = sheetno + 1.
* SET PROPERTY OF application 'Visible' = 1.
* m_message.
IF sheetno = sheets.
GET PROPERTY OF application 'ACTIVESHEET' = worksheet.
m_message.
CALL METHOD OF application 'Worksheets' =
worksheet EXPORTING #1 = sheetno.
m_message.
CALL METHOD OF worksheet 'Activate'.
m_message.
GET PROPERTY OF application 'ACTIVESHEET' = worksheet.
m_message.
ENDIF.
ENDDO.
* 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.
FREE excel_tab.
* read clipboard into ABAP
CALL METHOD cl_gui_frontend_services=>clipboard_import
IMPORTING
data = excel_tab
EXCEPTIONS
cntl_error = 1
* ERROR_NO_GUI = 2
* NOT_SUPPORTED_BY_GUI = 3
OTHERS = 4
.
IF sy-subrc <> 0.
MESSAGE a037(alsmex).
ENDIF.
PERFORM separated_to_intern_convert TABLES excel_tab intern
USING ld_separator
sheetno.
REFRESH : it_data.
APPEND LINES OF intern TO it_data.
FREE intern.
* clear clipboard
REFRESH excel_tab.
CALL METHOD cl_gui_frontend_services=>clipboard_export
IMPORTING
data = excel_tab
CHANGING
rc = ld_rc
EXCEPTIONS
cntl_error = 1
* ERROR_NO_GUI = 2
* NOT_SUPPORTED_BY_GUI = 3
OTHERS = 4
.
CALL METHOD OF application 'QUIT'.
m_message.
* 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.