Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Issue in excel upload with ALSM Function module

0 Kudos
1,685

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

4 REPLIES 4

Sandra_Rossi
Active Contributor
0 Kudos
353

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
0 Kudos
353

This message was moderated.

0 Kudos
353

This message was moderated.

Former Member
0 Kudos
353

Hi,

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

if u get anything please mail me.

Many Thanks,

Sriveni D.