1 2 Previous Next 20 Replies Latest reply: Jan 17, 2013 3:58 AM by Hani RSS

    Read data from excel

    Hani
      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
          ole2.set_property (application, 'Visible', 'false');
          should be ole2.set_property (application, 'Visible', '0');
          • 2. Re: Read data from excel
            Hani
            Thanks for response

            I Try it, still same error.
            • 3. Re: Read data from excel
              vansul
              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
                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
                  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
                    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
                      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
                        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
                          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
                            same error
                            • 11. Re: Read data from excel
                              vansul
                              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
                                use ole2.lastexception to catch the error
                                • 13. Re: Read data from excel
                                  vansul
                                  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
                                    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