This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Jan 17, 2013 1:58 AM by Hani RSS

Read data from excel

Hani Explorer
Currently Being Moderated
Hi

I try to read data from excel but always get message non-oracle-excetpion for code
workbook := ole2.get_obj_property (workbooks, 'Open', args);
Full code
application := ole2.create_obj ('Excel.Application');
ole2.set_property (application, 'Visible', 'false');
workbooks := ole2.get_obj_property (application, 'Workbooks');
args := ole2.create_arglist;
ole2.add_arg (args, filename); -- file path and name
--*******************************
workbook := ole2.get_obj_property (workbooks, 'Open', args);
--*******************************
ole2.destroy_arglist (args);
args := ole2.create_arglist;
ole2.add_arg (args, 'Sheet1');
worksheet := ole2.get_obj_property (workbook, 'Worksheets', args);
ole2.destroy_arglist (args);
Note that its work in window XP but not work in windows server 2008,
in both server have Microsoft office 2007

Edited by: Hani on Jan 6, 2013 4:27 PM
  • 1. Re: Read data from excel
    vansul Pro
    Currently Being Moderated
    ole2.set_property (application, 'Visible', 'false');
    should be ole2.set_property (application, 'Visible', '0');
  • 2. Re: Read data from excel
    Hani Explorer
    Currently Being Moderated
    Thanks for response

    I Try it, still same error.
  • 3. Re: Read data from excel
    vansul Pro
    Currently Being Moderated
    example of declaring the varaibles name:-


    DECLARE
    kAccess client_ole2.obj_type;
    kdb client_ole2.obj_type;
    ktables client_ole2.obj_type;
    krec client_ole2.obj_type;
    kcurr client_ole2.obj_type;
    begin
    kaccess:=client_ole2.create_obj('Access.application);
    kdb:=client_ole2.invoke_obj(kaccess,'open','D:\acc.mdb');
    ktables:=client_ole2.get_obj_property(kdb,'tables',1);

    //then process teh data of the table which is at 1 using another
    krec:=client_ole2.invoke_obj(ktables,'count');
    for s in 1 .. krec
    loop
    /// code for sinserting the current records data into the oracle tables.
    kcurr:=client_ole2.get_obj_type(ktables,'recordset',s);
    // then access each filed in kcurr and then insert in oracle table

    end loop;

    end;

    the above code is not tested your can alter any syntatical error if any.

    you have to declare the variables
  • 4. Re: Read data from excel
    vansul Pro
    Currently Being Moderated
    replace
    worksheet := ole2.get_obj_property (workbook, 'Worksheets', args);
    with
    worksheet := ole2.get_obj_property (workbook, 'Worksheets', 1);
  • 5. Re: Read data from excel
    Hani Explorer
    Currently Being Moderated
    Thanks, but
    My object is Excel.Application
    can tell me if there is wrong in my code or send me code for excel
  • 6. Re: Read data from excel
    Hani Explorer
    Currently Being Moderated
    The error will occurred before this statement

    Its happen in
    workbook := ole2.get_obj_property (workbooks, 'Open', args);
  • 7. Re: Read data from excel
    vansul Pro
    Currently Being Moderated
    instead of sheet1 use only 1 in the arglist then it will access the sheet1 and 2 for sheet2 and so on...
    you can use activesheet aslo insteaod of numbers.
  • 8. Re: Read data from excel
    Hani Explorer
    Currently Being Moderated
    The error will occurred before this statement

    Its happen in
    workbook := ole2.get_obj_property (workbooks, 'Open', args);
    so if i change Sheet1 to 1 nothing happen
  • 9. Re: Read data from excel
    vansul Pro
    Currently Being Moderated
    try
    workbook := ole2.invoke_obj (workbooks, 'Open', args);
    you can use full name instead of args as
    workbook := ole2.invoke_obj (workbooks, 'Open', 'd:\kul\myfile.xls');
  • 10. Re: Read data from excel
    Hani Explorer
    Currently Being Moderated
    same error
  • 11. Re: Read data from excel
    vansul Pro
    Currently Being Moderated
    you line excel.application has created so many threads at this time you can see at your task manger.
    you kill all the excel application now running and then try it again.
    as your excel application is now running with so many instances..

    in the command prompt use c:\> takslist to see the tasklist.
  • 12. Re: Read data from excel
    vansul Pro
    Currently Being Moderated
    use ole2.lastexception to catch the error
  • 13. Re: Read data from excel
    vansul Pro
    Currently Being Moderated
    begin
    kExcel:=client_ole2.create_obj('Excel.application); ---- open application
    kworkbook:=client_ole2.invoke_obj(kExcel,'open','D:\acc.xls'); ----------open xls file
    ksheet:=client_ole2.get_obj_property(kworkbook,'worksheets',1); ------- access sheet1
    etc....
  • 14. Re: Read data from excel
    vansul Pro
    Currently Being Moderated
    application := ole2.create_obj ('Excel.Application');
    ole2.set_property (application, 'Visible', 'false'); -- use o instead of false
    workbooks := ole2.get_obj_property (application, 'Workbooks'); ------------- remove it
    args := ole2.create_arglist;
    ole2.add_arg (args, filename); -- file path and name
    --*******************************
    workbook := ole2.get_obj_property (workbooks, 'Open', args); --- use ole2.invoke_obj instead of ole2.get_obj_property
    --*******************************
    ole2.destroy_arglist (args);
    args := ole2.create_arglist;
    ole2.add_arg (args, 'Sheet1');
    worksheet := ole2.get_obj_property (workbook, 'Worksheets', args);
    ole2.destroy_arglist (args);
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points