Skip to Content

Names of the Sheets in Excel files

Hi people,

I´d like to know if ther is a function that read the name of the sheets in a excel file.

Because I have to do a validation with the name.

Tks

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

6 Answers

  • Posted on Jan 28, 2009 at 07:11 PM

    May be [this|excel-upload-to-internal-tables; help you.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 29, 2009 at 10:41 AM

    Sorry, but I didnt get any help with this

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 29, 2009 at 10:48 AM

    Chk this out...

    &----


    *& Report YMULTIXL *

    *& *

    &----


    *& *

    *& *

    &----


    REPORT YMULTIXL .

    DATA:

    oref_container TYPE REF TO cl_gui_custom_container,

    iref_control TYPE REF TO i_oi_container_control,

    iref_document TYPE REF TO i_oi_document_proxy,

    iref_spreadsheet TYPE REF TO i_oi_spreadsheet,

    iref_error TYPE REF TO i_oi_error.

    DATA:

    v_document_url TYPE c LENGTH 256,

    i_sheets TYPE soi_sheets_table,

    wa_sheets TYPE soi_sheets,

    i_data TYPE soi_generic_table,

    wa_data TYPE soi_generic_item,

    i_ranges TYPE soi_range_list.

    PARAMETERS:

    p_file TYPE localfile OBLIGATORY,

    p_rows TYPE i DEFAULT 100 OBLIGATORY, "Rows (Maximum 65536)

    p_cols TYPE i DEFAULT 10 OBLIGATORY. "Columns (Maximum 256)

    INITIALIZATION.

    CALL METHOD c_oi_container_control_creator=>get_container_control

    IMPORTING

    control = iref_control

    error = iref_error

    retcode =

    .

    IF iref_error->has_failed = 'X'.

    CALL METHOD iref_error->raise_message

    EXPORTING

    type = 'E'.

    ENDIF.

    CREATE OBJECT oref_container

    EXPORTING

    parent =

    container_name = 'CONT'

    style =

    lifetime = lifetime_default

    repid =

    dynnr =

    no_autodef_progid_dynnr =

    EXCEPTIONS

    cntl_error = 1

    cntl_system_error = 2

    create_error = 3

    lifetime_error = 4

    lifetime_dynpro_dynpro_link = 5

    OTHERS = 6.

    IF sy-subrc 0.

    MESSAGE e001(00) WITH 'Error while creating container'.

    ENDIF.

    CALL METHOD iref_control->init_control

    EXPORTING

    dynpro_nr = SY-DYNNR

    gui_container = ' '

    inplace_enabled = 'X'

    inplace_mode = 0

    inplace_resize_documents = ' '

    inplace_scroll_documents = ' '

    inplace_show_toolbars = 'X'

    no_flush = ' '

    parent_id = cl_gui_cfw=>dynpro_0

    r3_application_name = 'EXCEL CONTAINER'

    register_on_close_event = ' '

    register_on_custom_event = ' '

    rep_id = SY-REPID

    shell_style = 1384185856

    parent = oref_container

    name =

    autoalign = 'x'

    IMPORTING

    error = iref_error

    retcode =

    EXCEPTIONS

    javabeannotsupported = 1

    OTHERS = 2

    .

    IF iref_error->has_failed = 'X'.

    CALL METHOD iref_error->raise_message

    EXPORTING

    type = 'E'.

    ENDIF.

    CALL METHOD iref_control->get_document_proxy

    EXPORTING

    document_format = 'NATIVE'

    document_type = soi_doctype_excel_sheet

    no_flush = ' '

    register_container = ' '

    IMPORTING

    document_proxy = iref_document

    error = iref_error

    retcode =

    .

    IF iref_error->has_failed = 'X'.

    CALL METHOD iref_error->raise_message

    EXPORTING

    type = 'E'.

    ENDIF.

    AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

    To provide F4 help for the file

    PERFORM sub_file_f4.

    START-OF-SELECTION.

    CONCATENATE 'FILE://' p_file INTO v_document_url.

    CALL METHOD iref_document->open_document

    EXPORTING

    document_title = 'Excel'

    document_url = v_document_url

    no_flush = ' '

    open_inplace = 'X'

    open_readonly = ' '

    protect_document = ' '

    onsave_macro = ' '

    startup_macro = ''

    user_info =

    IMPORTING

    error = iref_error

    retcode =

    .

    IF iref_error->has_failed = 'X'.

    CALL METHOD iref_error->raise_message

    EXPORTING

    type = 'I'.

    LEAVE LIST-PROCESSING.

    ENDIF.

    CALL METHOD iref_document->get_spreadsheet_interface

    EXPORTING

    no_flush = ' '

    IMPORTING

    error = iref_error

    sheet_interface = iref_spreadsheet

    retcode =

    .

    IF iref_error->has_failed = 'X'.

    CALL METHOD iref_error->raise_message

    EXPORTING

    type = 'I'.

    LEAVE LIST-PROCESSING.

    ENDIF.

    CALL METHOD iref_spreadsheet->get_sheets

    EXPORTING

    no_flush = ' '

    updating = -1

    IMPORTING

    sheets = i_sheets

    error = iref_error

    retcode =

    .

    IF iref_error->has_failed = 'X'.

    CALL METHOD iref_error->raise_message

    EXPORTING

    type = 'I'.

    LEAVE LIST-PROCESSING.

    ENDIF.

    LOOP AT i_sheets INTO wa_sheets.

    CALL METHOD iref_spreadsheet->select_sheet

    EXPORTING

    name = wa_sheets-sheet_name

    no_flush = ' '

    IMPORTING

    error = iref_error

    retcode =

    .

    IF iref_error->has_failed = 'X'.

    EXIT.

    call method iref_error->raise_message

    exporting

    type = 'E'.

    ENDIF.

    CALL METHOD iref_spreadsheet->set_selection

    EXPORTING

    top = 1

    left = 1

    rows = p_rows

    columns = p_cols.

    CALL METHOD iref_spreadsheet->insert_range

    EXPORTING

    name = 'Test'

    rows = p_rows

    columns = p_cols

    no_flush = ''

    IMPORTING

    error = iref_error.

    IF iref_error->has_failed = 'X'.

    EXIT.

    call method iref_error->raise_message

    exporting

    type = 'E'.

    ENDIF.

    REFRESH i_data.

    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

    .

    DELETE i_data WHERE value IS INITIAL OR value = space.

    ULINE.

    WRITE:/1 wa_sheets-sheet_name COLOR 3.

    ULINE.

    LOOP AT i_data INTO wa_data.

    WRITE:(50) wa_data-value.

    AT END OF row.

    NEW-LINE.

    ENDAT.

    ENDLOOP.

    ENDLOOP.

    CALL METHOD iref_document->close_document

    EXPORTING

    do_save = ' '

    no_flush = ' '

    IMPORTING

    error = iref_error

    has_changed =

    retcode =

    .

    IF iref_error->has_failed = 'X'.

    CALL METHOD iref_error->raise_message

    EXPORTING

    type = 'I'.

    LEAVE LIST-PROCESSING.

    ENDIF.

    CALL METHOD iref_document->release_document

    EXPORTING

    no_flush = ' '

    IMPORTING

    error = iref_error

    retcode =

    .

    IF iref_error->has_failed = 'X'.

    CALL METHOD iref_error->raise_message

    EXPORTING

    type = 'I'.

    LEAVE LIST-PROCESSING.

    ENDIF.

    &----


    *& Form SUB_FILE_F4

    &----


    F4 help for file path

    -


    FORM sub_file_f4 .

    DATA:

    l_desktop TYPE string,

    l_i_files TYPE filetable,

    l_wa_files TYPE file_table,

    l_rcode TYPE int4.

    Finding desktop

    CALL METHOD cl_gui_frontend_services=>get_desktop_directory

    CHANGING

    desktop_directory = l_desktop

    EXCEPTIONS

    cntl_error = 1

    error_no_gui = 2

    not_supported_by_gui = 3

    OTHERS = 4.

    IF sy-subrc 0.

    MESSAGE e001(00) WITH

    'Desktop not found'.

    ENDIF.

    Update View

    CALL METHOD cl_gui_cfw=>update_view

    EXCEPTIONS

    cntl_system_error = 1

    cntl_error = 2

    OTHERS = 3.

    CALL METHOD cl_gui_frontend_services=>file_open_dialog

    EXPORTING

    window_title = 'Select Excel file'

    default_extension = '.xls'

    default_filename =

    file_filter = '.xls'

    with_encoding =

    initial_directory = l_desktop

    multiselection =

    CHANGING

    file_table = l_i_files

    rc = l_rcode

    user_action =

    file_encoding =

    EXCEPTIONS

    file_open_dialog_failed = 1

    cntl_error = 2

    error_no_gui = 3

    not_supported_by_gui = 4

    OTHERS = 5

    .

    IF sy-subrc 0.

    MESSAGE e001(00) WITH 'Error while opening file'.

    ENDIF.

    READ TABLE l_i_files INDEX 1 INTO l_wa_files.

    IF sy-subrc = 0.

    p_file = l_wa_files-filename.

    ELSE.

    MESSAGE e001(00) WITH 'Error while opening file'.

    ENDIF.

    ENDFORM. " SUB_FILE_F4

    Hope this helps..

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 29, 2009 at 11:20 AM

    Hi,

    Check the below Thread,

    download-to-multiple-sheet-excel-in-the-background

    Regards,

    Anki Reddy

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 29, 2009 at 03:24 PM

    Karan this program doesnt work, its fulled of bugs

    I´m tring to use OLE Objects, but it isnt working yet.

    Look what im trying:

    INCLUDE ole2incl.

    DATA: application TYPE ole2_object,

    workbook TYPE ole2_object,

    sheet TYPE ole2_object,

    teste(80),

    ws TYPE ole2_object,

    cells TYPE ole2_object.

    CONSTANTS: row_max TYPE i VALUE 256.

    DATA index TYPE i.

    DATA: BEGIN OF itab1 OCCURS 0, first_name(10), END OF itab1.

    DATA: BEGIN OF itab2 OCCURS 0, last_name(10), END OF itab2.

    DATA: BEGIN OF itab3 OCCURS 0, formula(50), END OF itab3.

    ************************************************************************

    START-OF-SELECTION.

    CREATE OBJECT application 'excel.application'.

    SET PROPERTY OF application 'visible' = 1.

    CALL METHOD OF application 'Workbooks' = workbook.

    CALL METHOD OF workbook 'Open'

    EXPORTING #1 = 'c:EM_0488ContratosME2008 error2.xls'."'c:EM 0436.xls'. "filename

    CALL METHOD OF application 'sheets' = sheet

    EXPORTING #1 = 1.

    GET PROPERTY OF sheet 'Name' = teste.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 27, 2009 at 05:57 PM

    Hi people,

    I got it, I used OLE objects, here is what I did:

    INCLUDE ole2incl.

    DATA: application TYPE ole2_object,

    workbook TYPE ole2_object,

    worksheet TYPE ole2_object,

    gs_activesheet TYPE ole2_object.

    • 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

    • open file in Excel

    CREATE OBJECT application 'Excel.Application'.

    m_message.

    CALL METHOD OF application 'Workbooks' = workbook.

    m_message.

    CALL METHOD OF workbook 'Open' EXPORTING #1 = 'c:EM 0436.xls'.

    m_message.

    CALL METHOD OF application 'WorkSheets' = gs_activesheet

    EXPORTING

    #1 = 5.

    CALL METHOD OF gs_activesheet 'Activate'.

    DATA: TESTE(250).

    " Verifica si la hoja deseada existe

    IF sy-subrc NE 0.

    CALL METHOD OF application 'QUIT'.

    m_message.

    RAISE not_found.

    ENDIF.

    GET PROPERTY OF application 'ACTIVESHEET' = worksheet.

    m_message.

    GET PROPERTY OF worksheet 'NAME' = TESTE.

    CALL METHOD OF application 'QUIT'.

    WRITE teste.

    FREE OBJECT worksheet. m_message.

    FREE OBJECT workbook. m_message.

    FREE OBJECT application. m_message.

    THANKS

    Add a comment
    10|10000 characters needed characters exceeded

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.