5 Replies Latest reply: Nov 27, 2012 11:44 PM by 949572 RSS

    assign hyperlink via ole2 in excel cell

    949572
      I have an form which creates excel sheet. Everything works fine, but i want to assign hyperlink for excel cell.
      how can i do this?
      I am using oracle forms 10.1.2.02
        • 1. Re: assign hyperlink via ole2 in excel cell
          Marwim
          {message:id=10685003}
          • 2. Re: assign hyperlink via ole2 in excel cell
            949572
            i am not getting idea that how can i do this via OLE2.

            This code works fine in Excel VBA

            *ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _*
            *"Book%20Format%20A5%20size.xlsx", TextToDisplay:="RND"*

            how can i do this using OLE2??

            Edited by: Akarsh on Nov 16, 2012 10:55 PM
            • 3. Re: assign hyperlink via ole2 in excel cell
              949572
              anyone please help me to solve this..
              • 4. Re: assign hyperlink via ole2 in excel cell
                vansul
                Declare

                excelobj OLE2.OBJ_TYPE;
                workbookobj OLE2.obj_type;
                sheetsobj OLE2.OBJ_TYPE;
                activesheet OLE2.OBJ_TYPE;

                begin
                excelobj:=OLE2.create_object('excel.application');
                workbookobj:=OLE2.invoke_obj(excelobj,'open','D:\kul\new.xls');
                sheetsobj:=OLE2.invoke_obj(workbookobj,'Sheets');
                activesheet:=ole2.get_obj_property(sheetsobj,'ActiveSheet');

                ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= "Book%20Format%20A5%20size.xlsx", TextToDisplay:="RND"
                ole2.invoke(activesheet,'ActiveSheet.Hyperlinks.Add Anchor','ActiveSheet.Hyperlinks.Add Anchor');
                ole2.invoke(activesheet,'ActiveSheet.Hyperlinks.Address','Book%20Format%20A5%20size.xlsx');
                ole2.invoke(activesheet,'ActiveSheet.Hyperlinks.TextToDisplay:,'RND');

                destroy_obj(...);
                destroy_obj(...);
                destroy_obj(...);destroy_obj(...);

                end;
                • 5. Re: assign hyperlink via ole2 in excel cell
                  949572
                  i have tried the following code but i am not able to set hyperlink in excel..

                  Declare
                  application ole2.OBJ_TYPE;
                  workbook1 ole2.OBJ_TYPE;
                  workbooks1 ole2.OBJ_TYPE;
                  worksheet1 ole2.OBJ_TYPE;
                  worksheets1 ole2.OBJ_TYPE;
                  args1 ole2.List_Type;
                  file1 varchar2(300):='D:\final_new_backs_of_factory(Printout)_'||sysdate||'.xls';
                  cell ole2.obj_type;

                  begin

                  application := ole2.create_obj('excel.application');
                  ole2.set_property(application,'visible','true');
                  args1 := OLE2.Create_Arglist;
                  workbooks1 := OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');
                  workbook1 := OLE2.INVOKE_OBJ(workbooks1,'Add');
                  worksheets1 := OLE2.GET_OBJ_PROPERTY(workbook1, 'sheets');
                  worksheet1 := OLE2.INVOKE_OBJ(worksheets1,'Add');
                  OLE2.SET_PROPERTY(worksheet1, 'Name', 'akarsh');
                  OLE2.DESTROY_ARGLIST(args1);

                  --ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= "Book%20Format%20A5%20size.xlsx", TextToDisplay:="RND"

                  args1 := ole2.create_arglist;
                  ole2.add_arg(args1,1);
                  ole2.add_arg(args1,1);
                  cell:=ole2.get_obj_property(worksheet1,'cells',args1);
                  ole2.set_property(cell,'value','rupal');
                  ole2.destroy_arglist(args1);

                       ole2.set_property(ole2.get_obj_property(cell,'font'),'Name','Cambria');
                       ole2.set_property(ole2.get_obj_property(cell,'font'),'size',11);
                       ole2.invoke(ole2.get_obj_property(worksheet1,'Hyperlinks'),'Add Anchor',cell);
                       ole2.invoke(ole2.get_obj_property(worksheet1,'Hyperlinks'),'Address','Book%20Format%20A5%20size.xlsx');
                       ole2.invoke(ole2.get_obj_property(worksheet1,'Hyperlinks'),'TexttoDisplay','Click');
                       
                  --ole2.invoke(cell,'Hyperlinks.Add Anchor',args1);
                  --ole2.invoke(cell,'Hyperlinks.Address','Book%20Format%20A5%20size.xlsx');
                  --ole2.invoke(cell,'Hyperlinks.TextToDisplay','RND');

                  ole2.release_obj(cell);
                  OLE2.RELEASE_OBJ(worksheet1);
                  OLE2.RELEASE_OBJ(worksheets1);
                  OLE2.RELEASE_OBJ(workbook1);
                  OLE2.RELEASE_OBJ(workbooks1);
                  OLE2.INVOKE(application,'QUIT');
                  OLE2.RELEASE_OBJ(application);
                  end;

                  Anyone please help m to solve this...