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

I have an Excel spreadsheet with many rows that I want to automate an operation on.

Test.XLS contains

A B
Name Email
Lee Yes

Yes is actually a hyperlink =javascript(LEEM)=Address )
when I drag the mouse underneath the Yes -
I see the Edit Hyperlink content
=mailto:Lee@somewhere.com

The result I want is a way to rewrite the row as
A1 B1 C1
Lee Yes mailto:Lee@somewhere.com
as if I had manually,
right-clicked on the YES hyperlink
to go to
Hyperlink.. =>Edit Hyperlink => and
manually copied the email address and then pasted it into
C1
Any suggestions on how to access the Email address via Win32::OLE and Perl?
First attempt was

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';

$Kount = 0;

$Win32::OLE::Warn = 3;                                # die on errors.
+..

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit');  # get already ac
+tive Excel
                                                      # application or
+ open new

my $Book = $Excel->Workbooks->Open('c:\lee\Test.xls'); # open Excel fi
+le

my $Sheet = $Book->Worksheets(1);                         # select wor
+ksheet number 1
# $Sheet->Range("i4:i4")->{'Value'};       # get the contents of the A
+ddress column

$hlv=$Sheet->Hyperlinks(1)->Address;


print "hlv=$hlv \n";




$Book->Close;

[download]

Replies are listed 'Best First'.
Re: Excel Hyperlink Contents
by Samy_rio (Vicar) on Aug 22, 2006 at 05:47 UTC

    Hi Ad Aspera, Try Like this,

    use strict;
use warnings;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3;                                # die on errors.
+..

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit');  # get already ac
+tive Excel
                                                      # application or
+ open new
my $Book = $Excel->Workbooks->Open('C:\Documents and Settings\velusamy
+r\My Documents\tes.xls'); # open Excel file

my $Sheet = $Book->Worksheets(1);                         # select wor
+ksheet number 1

my $lastrow = $Sheet->UsedRange->Find({What=>"*",
    SearchDirection=>xlPrevious,
    SearchOrder=>xlByRows})->{Row};

$Sheet->Cells(1, 3)->{Value} = "Address";

for (2..$lastrow)
{
    $Sheet->Cells($_, 3)->{Value} = $Sheet->Hyperlinks($_ - 1)->Addres
+s;
}

$Book->Save();

__END__
Name    Email    Address
Lee1    Yes    mailto:Lee@somewhere.com1
Lee2    Yes    mailto:Lee@somewhere.com2
Lee3    Yes    mailto:Lee@somewhere.com3
Lee4    Yes    mailto:Lee@somewhere.com4
Lee5    Yes    mailto:Lee@somewhere.com5
Lee6    Yes    mailto:Lee@somewhere.com6

    [download]

    Regards,
    Velusamy R.

    eval"print uc\"\\c$_\""for split'','j)@,/6%@0%2,`e@3!-9v2)/@|6%,53!-9@2~j';

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom