Re: Testing for Excel hyperlinks with Win32::OLE

For worst-case, your solution doesn't seem too bad .. Might be able to find a better way by poking around with recording a macro and seeing what code comes out ... For example, I just made this (actions i took are in comments):

Sub Macro1()
' Right-click a cell
    Range("C10").Select
' From the context menu, click "Hyperlink...", and in there fill in th
+e dialog
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
        "http://blah.example.com", TextToDisplay:="foo"
    Range("C10").Select
' Right-click cell again, do "Edit Hyperlink..." and s/foo/bar/
    Selection.Hyperlinks(1).TextToDisplay = "bar"
End Sub
[download]

But what's of interest here is it seems that Excel keeps track of all the hyperlinks in the ActiveSheet.Hyperlinks collection, so you could hash that up on it's Anchor property so you know what cells all of the hyperlinks are in ...
Update: I was able to access that collection, but i can't seem to get the Anchor property from it..

use strict;
use warnings;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Variant;
use Win32::OLE::NLS qw(:LOCALE :DATE);
$Win32::OLE::Warn = 3; # Die on Errors.
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
        || Win32::OLE->new('Excel.Application', 'Quit');
my $Book = $Excel->Workbooks->Open('C:\Documents and Settings\David\hy
+perlinks.xls'); 
my $Sheet = $Book->Worksheets("Sheet1");
$Sheet->Activate();    
foreach ( 1.. $Sheet->Hyperlinks->Count() ){
  print "\n";
  warn $Sheet->Hyperlinks($_)->TextToDisplay;
  warn $Sheet->Hyperlinks($_)->Address;
#  warn $Sheet->Hyperlinks($_)->Anchor;  # This errors with "Member no
+t found"
}
$Book = $Excel->Workbooks->Close();
[download]

Comment on Re: Testing for Excel hyperlinks with Win32::OLE Select or Download Code

Replies are listed 'Best First'.
Re^2: Testing for Excel hyperlinks with Win32::OLE by Albannach (Monsignor) on Sep 27, 2005 at 17:32 UTC
It doesn't seem like quite the right approach, but I was able to locate the cell containing the hyperlink using `$Sheet->Hyperlinks($_)->Range->Column` and `$Sheet->Hyperlinks($_)->Range->Row`. -- I'd like to be able to assign to an luser	[reply] [d/l] [select]
Re^3: Testing for Excel hyperlinks with Win32::OLE by davidrw (Prior) on Sep 27, 2005 at 18:07 UTC
Ah .. So you could do this at the beginning: `my $cellsWithHyperlinks = {}; $cellsWithHyperlinks->{ $_->Column }->{ $_->Row }++ for map { $Sheet->Hyperlinks($_)->Range } 1 .. $Sheet->Hyperlinks->C +ount();` [download] And then while you're looping over the cells to write out the HTML: `foreach my $col ( 1 .. 10 ){ warn "Col # $col has hyperlinks somewhere" if exists $cellsWithHyper +links->{$col}; foreach my $row ( 1 .. 10 ){ warn "(Col,Row)=($col,$row) has a hyperlink" if exists $cellsWithH +yperlinks->{$col}->{$row}; } }` [download]	[reply] [d/l] [select]
Re^4: Testing for Excel hyperlinks with Win32::OLE by sz (Friar) on Sep 27, 2005 at 19:53 UTC
Excellent! I think this last bit of code will get me the rest of the way there. I had come across the idea of recording a macro and using it as a basis for my perl code, but had not tried it. The MS Office object model has been a real pain to parse for my overtired brain. Thanks for the help. You've both saved me much time and aggravation. Best, Sergej	[reply]