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]
      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]
[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]

Back to Seekers of Perl Wisdom