4 Replies Latest reply: Dec 19, 2012 6:12 AM by 595599 RSS

    set hyperlink in excel

    kaminanikamini
      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
          anyone????
          Please help me.
          • 2. Re: set hyperlink in excel
            Andreas Weiden
            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
              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
                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);