This discussion is archived
5 Replies Latest reply: Nov 27, 2012 9:44 PM by 949572 RSS

assign hyperlink via ole2 in excel cell

949572 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    {message:id=10685003}
  • 2. Re: assign hyperlink via ole2 in excel cell
    949572 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    anyone please help me to solve this..
  • 4. Re: assign hyperlink via ole2 in excel cell
    vansul Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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...

Legend

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