Creating Hyperlink with a Excel Sheet

DVCHAL has asked for the wisdom of the Perl Monks concerning the following question:

Hi monks, I wanted to know how to create/Set hyperlinks within the Excel Sheet using Win32::OLE. To navigate from one to sheet to other sheet in the same xlsx. I understand the Syntax would be,

$worksheet->Hyperlinks->Add({Anchor  => $range,   Address         =>  
+$adr,
TextToDisplay   =>  $txt,
ScreenTip       =>  $tip1,
[download]

But what would be Value for Address to set the link to the same Excel?

Comment on Creating Hyperlink with a Excel Sheet Download Code

Replies are listed 'Best First'.
Re: Creating Hyperlink with a Excel Sheet by mendeepak (Scribe) on Dec 19, 2014 at 12:33 UTC
Reffer this Using Win32::OLE and Excel - Tips and Tricks and this Modifying Excel hyperlink using PERL and OLE.pm hope this helps Deepak	[reply]
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.	[reply]
Re^3: Creating Hyperlink with a Excel Sheet by poj (Abbot) on Dec 19, 2014 at 13:37 UTC
Try #!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; [download] poj	[reply] [d/l]