1 Reply Latest reply: Mar 5, 2013 12:46 PM by InoL RSS

    Export To Excel

    Moazam Shareef
      Hi guyz,

      I'm exporting some data from forms to excel its working fine. but i when i'm cancelling the excel file inbetween exporting its showing the below error, how can i resolve this issue?

      How can i set the alerts if the file was cancelled inbetween exporting?
      FRM-40735:WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-305500.
      I'm using the below code on WBP trigger.
      declare
       application ole2.obj_type;
       workbooks ole2.obj_type;
       workbook ole2.obj_type;
       worksheets ole2.obj_type;
       worksheet ole2.obj_type;
       cell ole2.obj_type;
       args ole2.list_type;
       rowcount integer;
       --out_file Text_IO.File_Type;
       --flnm varchar2(200);
       cursor c1 is 
       select EMPID,NAME1_ENG,NAME2_ENG,NAME3_ENG,NAME4_ENG,DEPT_NAME
       from MN_EMPLOYEE
       order by EMPID;
      
       begin
       /*flnm := GET_FILE_NAME('c:\', 'file_name.xls', 'XLS Files (*.xls)|*.xls|', NULL, SAVE_FILE, TRUE);
      
       out_file:=Text_IO.Fopen(flnm, 'w');*/
       application := ole2.create_obj('Excel.Application');
       ole2.set_property(application,'Visible','True');
       workbooks := ole2.get_obj_property
       (application,'Workbooks');
       workbook := ole2.invoke_obj(workbooks,'Add');
       worksheets := ole2.get_obj_property
       (workbook,'Worksheets');
       worksheet := ole2.invoke_obj(worksheets,'Add');
      
       rowcount := 0;
       for rec1 in c1 loop
       rowcount := rowcount + 1;
       args := ole2.create_arglist;
       ole2.add_arg(args,rowcount);
       ole2.add_arg(args,1);
       cell := ole2.get_obj_property(worksheet,'Cells',args);
       ole2.destroy_arglist(args);
       ole2.set_property(cell,'Value',rec1.EMPID);
       ole2.release_obj(cell);
      
       args := ole2.create_arglist;
       ole2.add_arg(args,rowcount);
       ole2.add_arg(args,2);
       cell := ole2.get_obj_property(worksheet,'Cells',args);
       ole2.destroy_arglist(args);
       ole2.set_property(cell,'Value',rec1.NAME1_ENG);
       ole2.release_obj(cell);
       
       args := ole2.create_arglist;
       ole2.add_arg(args,rowcount);
       ole2.add_arg(args,3);
       cell := ole2.get_obj_property(worksheet,'Cells',args);
       ole2.destroy_arglist(args);
       ole2.set_property(cell,'Value',rec1.NAME2_ENG);
       ole2.release_obj(cell);
       
       args := ole2.create_arglist;
       ole2.add_arg(args,rowcount);
       ole2.add_arg(args,4);
       cell := ole2.get_obj_property(worksheet,'Cells',args);
       ole2.destroy_arglist(args);
       ole2.set_property(cell,'Value',rec1.NAME3_ENG);
       ole2.release_obj(cell);
       
       args := ole2.create_arglist;
       ole2.add_arg(args,rowcount);
       ole2.add_arg(args,5);
       cell := ole2.get_obj_property(worksheet,'Cells',args);
       ole2.destroy_arglist(args);
       ole2.set_property(cell,'Value',rec1.NAME4_ENG);
       ole2.release_obj(cell);
       end loop;
      
       ole2.release_obj(worksheet);
       ole2.release_obj(worksheets);
       ole2.release_obj(workbook);
       ole2.release_obj(workbooks);
       ole2.release_obj(application);
       ALERT_MESSAGES('Sucessfully Exported!');
       end;
      I'm using forms 6i oracle 9i.

      Regards
        • 1. Re: Export To Excel
          InoL
          What exactly do you mean by "cancelling the excel file inbetween exporting"? The user just closes Excel while it is still retrieving data?
          Add an exception handler to your code for this. Something like this:
          file_unavailable     exception;
          pragma exception_init (file_unavailable, -305500);
          
          begin
          ...
          exception
             when file_unavailable then <do something>;
             when others then raise;
          end;