Re^2: Creating Hyperlink with a Excel Sheet

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.

Comment on Re^2: Creating Hyperlink with a Excel Sheet

Replies are listed 'Best First'.
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]

Replies are listed 'Best First'.

Re^3: Creating Hyperlink with a Excel Sheet
by poj (Abbot) on Dec 19, 2014 at 13:37 UTC

#!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]

[reply]
[d/l]