Modifying Excel hyperlink using PERL and OLE.pm

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

I've looked far and wide, even so much as going to Microsoft's Excel hyperlinks reference page.

What is the structure/syntax one would use to edit an Excel cell's hyperlink value, using only Perl and Win32::OLE.pm?

I can read cell values and hyperlinks, and change cell values, but cant figure out how to modify the hyperlink.

Here are some of the things I've tried in vain:

• $worksheet->Range("D39")->Hyperlinks->{Address}="C:\\temp";
• $worksheet->Range("D39")->Hyperlinks->Add({Address => "C:\\temp"});
• $worksheet->Hyperlinks->Add({Address=>'C:\\temp'});
• $worksheet->Hyperlinks->Add({Anchor=>('D39'), Address=>'C:\\temp'});
• $cellObject->Hyperlinks->Add(Range('A2'), 'C:\\temp');

... and my personal favorite ...

$worksheet->Hyperlinks()->Add({ Address => "C:\\temp", Anchor => Selected, TextToDisplay => "AutoLink", });

I'd just like to know what structure will work.

Thanks,
-SplashCD

Comment on Modifying Excel hyperlink using PERL and OLE.pm

Replies are listed 'Best First'.
Re: Modifying Excel hyperlink using PERL and OLE.pm by guha (Priest) on Jul 23, 2004 at 18:13 UTC
On my way out the door .... `$worksheet->Hyperlinks->Add({ Anchor => $range1, Address => $adr, TextToDisplay => $txt, ScreenTip => $tip1, });` [download]	[reply] [d/l]
Re^2: Modifying Excel hyperlink using PERL and OLE.pm by SplashCD (Initiate) on Jul 23, 2004 at 20:02 UTC
Thank you guha! Your solution brings a very welcomed end to my days long search. I am indebted. Would you suggest any book or reference that I should look to for future OLE.pm quandaries? Regards, -SplashCD	[reply]
Re^3: Modifying Excel hyperlink using PERL and OLE.pm by guha (Priest) on Jul 27, 2004 at 07:46 UTC
here	[reply]
Re: Modifying Excel hyperlink using PERL and OLE.pm by jmcnamara (Monsignor) on Jul 23, 2004 at 23:10 UTC
Here is one way: #!/usr/bin/perl -w use strict; use Cwd; use Win32::OLE; use Win32::OLE::Const 'Microsoft Excel'; my $application = Win32::OLE->new("Excel.Application"); my $workbook = $application->Workbooks->Add; my $worksheet = $workbook->Worksheets(1); # Write a hyperlink my $range = $worksheet->Range("B2:B2"); $worksheet->Hyperlinks->Add({Anchor => $range, Address => "http://www.perl.com/"}); # Get current directory using Cwd.pm $workbook->SaveAs({FileName => cwd() . '/win32ole.xls'}); $workbook->Close; __END__ [download] P.S. I answered this earlier as well. -- John.	[reply] [d/l]


more useful options
	PerlMonks