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: 

How to read a excel file with more than one worksheet.

Former Member
0 Kudos

Hi,

I wanna read a sheet (anyone of it,not the first) in a excel.Who can help me!

best wishes,

Grant Chen

4 REPLIES 4

Former Member
0 Kudos

Hi Grant Chen,

Please check this link

Uploading multiple multitab Excel sheets or Ranges from Front end to SAP

http://www.sap-img.com/abap/uploading-multiple-multitab-excel-sheets-or-ranges.htm

Best regards,

raam

Former Member
0 Kudos

Hi Grant,

pls see the following pdf

help.sap.com/.../BBLibrary/Data%20Transfer%20Tool%20Documentation/Using_the_Data_Transfer_Tool_Part_I_CA.pdf

thanks

karthik

Former Member
0 Kudos

HI Grant,

Check this following link:

http://www.sapnet.ru/viewtopic.php?p=482&sid=144935daf959d9ca80a5b3b2b0802fa4

Hope this helps you.

Regards,

Chandra Sekhar

0 Kudos

Dear All,

Thank you all for your reply!

I modified SAP FM ZALSM_EXCEL_TO_INTERNAL_TABLE.

I add an IMPORTING parameter 'I_SHEET'

which stands for the sequence number of worksheet in the FM.

FUNCTION ZALSM_EXCEL_TO_INTERNAL_TABLE .
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  IMPORTING
*"     VALUE(FILENAME) LIKE  RLGRAP-FILENAME
*"     REFERENCE(I_SHEET) TYPE  I
*"     VALUE(I_BEGIN_COL) TYPE  I
*"     VALUE(I_BEGIN_ROW) TYPE  I
*"     VALUE(I_END_COL) TYPE  I
*"     VALUE(I_END_ROW) TYPE  I
*"  TABLES
*"      INTERN STRUCTURE  ALSMEX_TABLINE
*"  EXCEPTIONS
*"      INCONSISTENT_PARAMETERS
*"      UPLOAD_OLE
*"----------------------------------------------------------------------
*{   INSERT         EC6K900099                                        1

  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.
********************Modified by Grant 20080717************
  call METHOD of application 'Worksheets' = worksheet
  exporting
    #1 = I_SHEET.
  m_message.
  call method of worksheet 'Activate'.
*  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.

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

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

* quit Excel and free ABAP Object - unfortunately, this does not kill
* the Excel process
  CALL METHOD OF application 'QUIT'.
  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
*}   INSERT
ENDFUNCTION.

Edited by: Grant Chen on Jul 17, 2008 7:45 AM

Edited by: Grant Chen on Jul 17, 2008 7:46 AM