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

Open a sheet from an excel file

Hi,

I'm trying to read data from a specific sheet in a excel file, I'm using this code:

  • Start Excel

if h_appl-header = space or h_appl-handle = -1.

create object h_appl 'EXCEL.APPLICATION'.

if sy-subrc ne 0. message i002(sy) with sy-msgli. endif.

set property of h_appl 'VISIBLE' = 0.

endif.

  • Open file

call method of h_appl 'WORKBOOKS' = h_work.

call method of h_work 'OPEN' exporting #1 = p_cami.

  • Set Excel Sheet

call method of h_appl 'Worksheets' = sheet

exporting #1 = 2. "Planilha 2

call method of sheet 'Activate'.

call method of sheet 'OPEN' exporting #1 = w_sheet_name. "sheet name

But it's not working, it opens the excel file, but not the correct sheet, it's always opening the second sheet.

I don't Konw what is happening, because I'm passing the name of the sheet in "w_sheet_name".

Any help is wellcome.

Thanks.

Alexandre Nogueira.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Nov 17, 2005 at 07:52 PM

    Hi,

    Even though you are giving the name, you are also passing '2' as worksheet:

    • Set Excel Sheet

    call method of h_appl 'Worksheets' = sheet

    exporting #1 = 2. "Planilha 2

    I think the worksheet number is taking precedence over the name.

    Try to change to:

    • Set Excel Sheet

    call method of h_appl 'Worksheets' = sheet

    exporting #1 = 1. "Planilha 1

    Hope this helps,

    Bhanu

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 18, 2005 at 06:06 AM

    Hi Alexandre,

    1. I had tried your query with my own .xls file.

    2. Let say

    filename = "C:\mat.xls

    it contains 5 Sheets or any number of sheets.

    We will open "Sheet5"

    3. Just try my Z Code.

    It will 100% work.

    It will open the excel file

    with SHEET5 on screen.

    4. I have used some other METHODS of OLE(Excel)

    This is one of the way.

    Other ways may be possible.

    5. After opening the required sheet (SHEET5 in our case)

    the rest program for fetching data,

    i suppose u will be knowing already or workout!

    6. The code goes as below :

    REPORT abc.

    *----


    Data

    TYPE-POOLS ole2.

    DATA excel TYPE ole2_object.

    DATA workbook TYPE ole2_object.

    DATA aw TYPE ole2_object. "-- active workbook

    DATA sheets TYPE ole2_object.

    DATA sheet TYPE ole2_object.

    DATA cnt TYPE i.

    DATA nm(250) TYPE c.

    DATA : filename(100) TYPE c.

    DATA : sheetname(100) TYPE c.

    START-OF-SELECTION.

    *----


    Variables

    filename = 'C:\MAT.XLS'.

    sheetname = 'Sheet5'.

    *----


    Open OLE

    CREATE OBJECT excel 'Excel.Application' .

    SET PROPERTY OF excel 'Visible' = 1.

    CALL METHOD OF excel 'Workbooks' = workbook.

    CALL METHOD OF workbook 'Open'

    EXPORTING

    #1 = filename.

    CALL METHOD OF excel 'ActiveWorkbook' = aw.

    CALL METHOD OF aw 'Sheets' = sheet

    EXPORTING

    #1 = sheetname.

    CALL METHOD OF aw 'Sheets' = sheets.

    GET PROPERTY OF sheets 'Count' = cnt.

    GET PROPERTY OF sheet 'Name' = nm.

    CALL METHOD OF sheet 'Activate'.

    *----


    Display for checking purpose

    WRITE :/ cnt , nm.

    *----


    Regards,

    Amit M.

    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.