Issue in excel upload with ALSM Function module

former_member227921 · ‎05-07-2016

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.

Sandra_Rossi · ‎05-07-2016

I had an issue a long time ago, there was some limitations because the internal table was not a table of 8000 characters (note 1354187 - Use correct line length for clipboard import , but it isn't released any more). Although I couldn't understand the reason to use 8000 characters, it worked.

Former Member · ‎05-23-2016

This message was moderated.

former_member227921 · ‎05-24-2016

This message was moderated.

Former Member · ‎05-24-2016

Hi,

i have cleared the object. my mail id dsriveni89@gmail.com.

if u get anything please mail me.

Many Thanks,

Sriveni D.