Skip to Content
author's profile photo Former Member
Former Member

Read Excel 2007 multi sheets file

Hi guys,

I'm trying the code found here

http://wiki.sdn.sap.com/wiki/display/Snippets/ReadmultiplesheetsofanExcelfileintoSAPthroughABAP

Mi aim is to upload an excel 2007 document with multi sheets.

The code doesn't work properly at the command

CALL METHOD iref_spreadsheet->get_ranges_data

EXPORTING

  • no_flush = ' '

all = 'X'

  • updating = -1

  • rangesdef =

IMPORTING

contents = i_data

error = iref_error

  • retcode =

CHANGING

ranges = i_ranges

I'm developing in sap netweaver 7.0

Any suggetion???

Many thanks.

Edited by: Salvatore Bruno on Apr 21, 2011 12:38 PM

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Posted on Apr 21, 2011 at 02:46 PM

    Hello Salvatore,

    I have tried to do this once. I think it is not possible unless you know the names of the sheets.

    I used the FM ALSM_EXCEL_TO_INTERNAL_TABLE and modified it slightly:

    FUNCTION Z_ALSM_EXCEL_TO_INTERNAL_TABLE .

    *"----

    -


    ""Lokale Schnittstelle:

    *" 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(P_SHNAME) TYPE C

    *" TABLES

    *" INTERN STRUCTURE ALSMEX_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,

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

    CALL METHOD OF application 'WORKSHEETS' = sheet

    EXPORTING

    #1 = p_shname.

    CALL METHOD OF sheet 'ACTIVATE'.

    FREE OBJECT sheet.

    • set property of application 'Visible' = 1.

    • m_message.

    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

    ENDFUNCTION.

    Then I call and pass the name of the sheet.

    call function 'Z_ALSM_EXCEL_TO_INTERNAL_TABLE'

    exporting

    FILENAME = P_FILE

    I_BEGIN_COL = BEG_COL1

    I_BEGIN_ROW = 4

    I_END_COL = BEG_COL1

    I_END_ROW = END_ROW

    P_SHNAME = SHNAME

    tables

    INTERN = ITAB

    exceptions

    INCONSISTENT_PARAMETERS = 1

    UPLOAD_OLE = 2

    others = 3.

    Best Regards

    Diana Abu Gosh

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Apr 21, 2011 at 04:05 PM

    Hi Salvatore Bruno,

    The code doesn't work properly at the command

    What doesn't work properly??

    Thanks.

    Sandra

    Add a comment
    10|10000 characters needed characters exceeded

    • I still don't understand what kind of trouble you have, it works perfectly, even with formulas (the method gets the calculated values)... If you need a working example, run program SAPRDEMO_SPREADSHEET_INTERFACE, you can interact between ABAP and Excel (and especially test GET_RANGES_DATA), you'll set that you can read the ranges from any sheet

      BR

      Sandra

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.