[prev in list] [next in list] [prev in thread] [next in thread]
List: perl-win32-users
Subject: Re: Modifying the Address property of an Excel Hyperlink object
From: D D Allen <dewey.allen () us ! ibm ! com>
Date: 2006-03-29 19:57:40
Message-ID: OFE9E13CAB.3EF15375-ON85257140.006C3FEB-85257140.006DA66A () us ! ibm ! com
[Download RAW message or body]
This is a multipart message in MIME format.
This is a multipart message in MIME format.
--=_alternative 006DA16E85257140_=
Content-Type: text/plain; charset="US-ASCII"
The 1,100+ page book "Excel 2003 VBA" has only minimal information on
Hyperlinks. As Hyperlinks are a "collection", I believe you need to "use
Win32::OLE::Enum" to be able to iterate through all of the Hyperlink
objects on a given worksheet. Once you find the "right" Hyperlink object
(based on the Hyperlink range address or display text or ...), you can
then change the address via a simple assignment to the address property of
that hyperlink object. The following sample code iterates through all of
the hyperlinks in one of my excel spreadsheets. To run it, you'll need
to change the assignment "my $excelfn =" and the assignment "my $Sheet =
$Book->Worksheets("...
Regards,
... Dewey
use strict;
use warnings;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Variant;
use Win32::Ole::Enum;
use Cwd 'abs_path';
Win32::OLE->Option(CP => Win32::OLE::CP_UTF8);
$Win32::OLE::Warn = 3; # die on errors...
my $absrundir = abs_path('.');
if (substr($absrundir,-1,1) ne '/') {
$absrundir .= '/';
}
print STDERR "current directory is '$absrundir'\n";
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit'); # get already active
Excel
# application or
open new
$Excel->{'Visible'} = 0;
$Excel->{'DisplayAlerts'} = 0;
my $excelfn = 'enter your excel filename here.xls'; #change this line to
specify a real excel filename in the current working directory
my $Book;
eval { $Book = $Excel->Workbooks->Open($absrundir . $excelfn); };
if ($@) {
print "Excel could not open and save $excelfn\n";
print $@;
$Excel->Quit;
exit(1);
}
print STDERR "Excel opened '$excelfn'\n";
my $Sheet = $Book->Worksheets('enter the worksheet name here that contains
the hyperlinks'); #change this line to specify a valid sheet name in
the excel file
my $hypercount = $Sheet->Hyperlinks->{Count};
print STDERR "there are $hypercount hyperlinks on this worksheet\n";
my $hlenum = Win32::OLE::Enum->new($Sheet->Hyperlinks);
my $i = 0;
while (my $hl = $hlenum->Next()) {
$i++;
print "($i) range = ", $hl->Range->Address, "\n";
print "($i) display text = ", $hl->{TextToDisplay}, "\n";
print "($i) address = ", $hl->{Address}, "\n";
# $hl->{Address} = 'some new address'; #update address property
as follows...
}
#$Book->Save; #if you want to save your changes
$Book->Close(0);
$Excel->Quit;
"Glen Plantz" <Glen.Plantz@mitchell.com>
Sent by: perl-win32-users-bounces@listserv.ActiveState.com
03/29/2006 12:52 PM
To
<perl-win32-users@listserv.ActiveState.com>
cc
Subject
Modifying the Address property of an Excel Hyperlink object with
Win32::OLE
Hi folks,
I need to edit the Address property of Hyperlink objects in Excel
worksheets. I did a google and found several examples of ADDING
hyperlinks, but could someone please send me an example of editing an
EXISTING hyperlink 'Address' property?
Thanks in advance for any help.
Glen Plantz _______________________________________________
Perl-Win32-Users mailing list
Perl-Win32-Users@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
--=_alternative 006DA16E85257140_=
Content-Type: text/html; charset="US-ASCII"
<br><font size=2 face="sans-serif">The 1,100+ page book "Excel 2003
VBA" has only minimal information on Hyperlinks. As Hyperlinks
are a "collection", I believe you need to "use Win32::OLE::Enum"
to be able to iterate through all of the Hyperlink objects on a given worksheet.
Once you find the "right" Hyperlink object (based on the
Hyperlink range address or display text or ...), you can then change the
address via a simple assignment to the address property of that hyperlink
object. The following sample code iterates through all of the hyperlinks
in one of my excel spreadsheets. To run it, you'll need to change
the assignment "my $excelfn =" and the assignment "my $Sheet
= $Book->Worksheets("...</font>
<br><font size=2 face="sans-serif"><br>
Regards,<br>
<br>
... Dewey<br>
</font>
<br><font size=2 face="sans-serif">use strict;</font>
<br><font size=2 face="sans-serif">use warnings;</font>
<br>
<br><font size=2 face="sans-serif">use Win32::OLE qw(in with);</font>
<br><font size=2 face="sans-serif">use Win32::OLE::Const 'Microsoft Excel';</font>
<br><font size=2 face="sans-serif">use Win32::OLE::Variant;</font>
<br><font size=2 face="sans-serif">use Win32::Ole::Enum;</font>
<br>
<br><font size=2 face="sans-serif">use Cwd 'abs_path';</font>
<br>
<br><font size=2 face="sans-serif">Win32::OLE->Option(CP => \
Win32::OLE::CP_UTF8);</font> <br><font size=2 face="sans-serif">$Win32::OLE::Warn = \
3; \
# die on errors...</font>
<br>
<br><font size=2 face="sans-serif">my $absrundir = abs_path('.');</font>
<br><font size=2 face="sans-serif">if (substr($absrundir,-1,1) ne '/')
{</font>
<br><font size=2 face="sans-serif"> $absrundir
.= '/';</font>
<br><font size=2 face="sans-serif">}</font>
<br><font size=2 face="sans-serif">print STDERR "current directory
is '$absrundir'\n";</font>
<br>
<br><font size=2 face="sans-serif">my $Excel = \
Win32::OLE->GetActiveObject('Excel.Application')</font> <br><font size=2 \
face="sans-serif"> || Win32::OLE->new('Excel.Application', 'Quit'); \
# get already active Excel</font> <br><font size=2 face="sans-serif"> \
\
\
# application or open new</font>
<br><font size=2 face="sans-serif">
</font>
<br><font size=2 face="sans-serif">$Excel->{'Visible'} = 0;</font>
<br><font size=2 face="sans-serif">$Excel->{'DisplayAlerts'} = 0;</font>
<br>
<br><font size=2 face="sans-serif">my $excelfn = 'enter your excel filename
here.xls'; #change this line to specify a real excel filename in
the current working directory</font>
<br><font size=2 face="sans-serif">my $Book;</font>
<br>
<br><font size=2 face="sans-serif">eval { $Book = \
$Excel->Workbooks->Open($absrundir
. $excelfn); };</font>
<br><font size=2 face="sans-serif"> </font>
<br><font size=2 face="sans-serif">if ($@) {</font>
<br><font size=2 face="sans-serif"> print
"Excel could not open and save $excelfn\n";</font>
<br><font size=2 face="sans-serif"> print
$@;</font>
<br><font size=2 face="sans-serif"> \
$Excel->Quit;</font> <br><font size=2 face="sans-serif"> \
exit(1);</font> <br><font size=2 face="sans-serif">}</font>
<br><font size=2 face="sans-serif">print STDERR "Excel opened \
'$excelfn'\n"; </font>
<br><font size=2 face="sans-serif">my $Sheet = $Book->Worksheets('enter
the worksheet name here that contains the hyperlinks');
#change this line to specify a valid sheet name in the excel
file</font>
<br><font size=2 face="sans-serif">my $hypercount = \
$Sheet->Hyperlinks->{Count};</font> <br><font size=2 face="sans-serif">print \
STDERR "there are $hypercount hyperlinks on this worksheet\n";</font>
<br><font size=2 face="sans-serif">my $hlenum = \
Win32::OLE::Enum->new($Sheet->Hyperlinks);</font> <br>
<br><font size=2 face="sans-serif">my $i = 0;</font>
<br><font size=2 face="sans-serif">while (my $hl = $hlenum->Next())
{</font>
<br><font size=2 face="sans-serif"> $i++;</font>
<br><font size=2 face="sans-serif"> print
"($i) range = ", $hl->Range->Address, "\n";</font>
<br><font size=2 face="sans-serif"> print
"($i) display text = ", $hl->{TextToDisplay}, "\n";</font>
<br><font size=2 face="sans-serif"> print
"($i) address = ", $hl->{Address}, "\n";</font>
<br><font size=2 face="sans-serif"> </font>
<br><font size=2 face="sans-serif"> #
$hl->{Address} = 'some new address'; #update address property
as follows...</font>
<br><font size=2 face="sans-serif">}</font>
<br>
<br><font size=2 face="sans-serif">#$Book->Save;
#if you want to save
your changes</font>
<br><font size=2 face="sans-serif">$Book->Close(0);</font>
<br><font size=2 face="sans-serif">$Excel->Quit;</font>
<br>
<br>
<br>
<br><font size=2 face="sans-serif"><br>
</font>
<br>
<br>
<br>
<table width=100%>
<tr valign=top>
<td width=40%><font size=1 face="sans-serif"><b>"Glen Plantz"
<Glen.Plantz@mitchell.com></b> </font>
<br><font size=1 face="sans-serif">Sent by: \
perl-win32-users-bounces@listserv.ActiveState.com</font> <p><font size=1 \
face="sans-serif">03/29/2006 12:52 PM</font> <td width=59%>
<table width=100%>
<tr valign=top>
<td>
<div align=right><font size=1 face="sans-serif">To</font></div>
<td><font size=1 face="sans-serif"><perl-win32-users@listserv.ActiveState.com></font>
<tr valign=top>
<td>
<div align=right><font size=1 face="sans-serif">cc</font></div>
<td>
<tr valign=top>
<td>
<div align=right><font size=1 face="sans-serif">Subject</font></div>
<td><font size=1 face="sans-serif">Modifying the Address property of an
Excel Hyperlink object with Win32::OLE</font></table>
<br>
<table>
<tr valign=top>
<td>
<td></table>
<br></table>
<br>
<br>
<br><font size=2 face="Arial">Hi folks,</font><font size=3> </font>
<p><font size=2 face="Arial">I need to edit the Address property of Hyperlink
objects in Excel worksheets. I did a google and found several examples
of ADDING hyperlinks, but could someone please send me an example of editing
an EXISTING hyperlink 'Address' property?</font>
<p><font size=2 face="Arial">Thanks in advance for any help.</font><font size=3>
</font>
<p><font size=2 face="Arial">Glen Plantz</font><font size=3> </font><tt><font \
size=2>_______________________________________________<br> Perl-Win32-Users mailing \
list<br> Perl-Win32-Users@listserv.ActiveState.com<br>
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs<br>
</font></tt>
<p>
--=_alternative 006DA16E85257140_=--
_______________________________________________
Perl-Win32-Users mailing list
Perl-Win32-Users@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic