Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical

Re: Creating Hyperlink with a Excel Sheet

by mendeepak (Scribe)
on Dec 19, 2014 at 12:33 UTC ( [id://1110846] : note . print w/replies, xml ) Need Help??

in reply to Creating Hyperlink with a Excel Sheet

Reffer this Using Win32::OLE and Excel - Tips and Tricks and this Modifying Excel hyperlink using PERL and hope this helps


Replies are listed 'Best First'.
Re^2: Creating Hyperlink with a Excel Sheet
by DVCHAL (Novice) on Dec 19, 2014 at 12:41 UTC
    Deepak, Thanks for the Response.

    these all stuffs only talks about the Hyperlink to the webpages and files outside. I want the Hyperlink to be created WITHIN SAME Excel(xlsx). i.e. hyperlink to sheet2 from sheet1 in myexcel.xlsx.

    How can I do this? I need only the Value of "Address" which has to be passed in above code snippet.
      #!perl use strict; use Win32::OLE::Const 'Microsoft Excel'; Win32::OLE->Option(Warn => 3); my $ex = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit'); my $wb = $ex->Workbooks->Open('c:\\temp\\Book1.xlsx') ; my $ws = $wb->sheets('Sheet1'); $ws->Hyperlinks->Add({ Anchor => $ws->Range("A1"), Address => $wb->{name}, SubAddress => "Sheet2!B2", TextToDisplay => "Link to Sheet2", ScreenTip => "Sheet2", }); # save and exit $wb->SaveAs( 'c:\\temp\\changed.xlsx' ); undef $wb; undef $ex;