This discussion is archived
4 Replies Latest reply: Dec 19, 2012 4:12 AM by 595599 RSS

set hyperlink in excel

kaminanikamini Newbie
Currently Being Moderated
I am importing one excel file INPUT.xls which contain one columnl with hyperlink which opens a PDF file.
From this file i am generating a new file with that hyperlinked column Which contain the same link as INPUT.xls file has.
How can i get that link from INPUT.xls file and set it in my output file OUTPUT.xls file

How can i do this.

I am using Oracle forms 10.1.2.02
  • 1. Re: set hyperlink in excel
    kaminanikamini Newbie
    Currently Being Moderated
    anyone????
    Please help me.
  • 2. Re: set hyperlink in excel
    Andreas Weiden Guru
    Currently Being Moderated
    I'm not quite sure in which way this is related to Oracle Forms? If you want to do that via OLE2, try to make it work in Word VBA first, then translate that code into OLE2.
  • 3. Re: set hyperlink in excel
    kaminanikamini 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...
  • 4. Re: set hyperlink in excel
    595599 Newbie
    Currently Being Moderated
    This section of code is not right
    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');

    replace it with the following:-
    Hyperlinks:=ole2.get_obj_property(worksheet1,'Hyperlinks');           
         args := ole2.create_arglist;
         ole2.ADD_ARG_obj(args,cell);     
         ole2.ADD_ARG(args,'Book Format A5 size.xlsx');      
         hyperlink := ole2.invoke_obj(Hyperlinks, 'add', args);      
         ole2.destroy_arglist(args);
         ole2.Release_Obj(hyperlink);
         ole2.Release_Obj(hyperlinks);

Legend

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