Getting list of hyperlinks from an Excel worksheet

Question

I want to change the path for a bunch of hyperlinks in an Excel spreadsheet. After searching Google, I came across a solutions to the problem of adding hyperlinks to spreadsheets, but not changing them. Microsoft showed how to something close with VBA here.

Since I want to edit every single hyperlink in my document, the key steps that I don't know how to solve are:

1. Get a list of hyperlink objects in Perl

2. Extract their addresses 1 by 1 and

3. Run a regular expression to make the path change

4. Store the updated path in the Hyperlink->object and repeat

I am new to using the OLE and am getting tripped up on (1). Here is what I have tried so far:

#!perl
use strict;
use warnings;
use 5.014;
use OLE;
use Win32::OLE::Const "Microsoft Excel";

my $file_name = 'C:\path\to\spreadsheet.xlsx';
my $excel = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;});
$excel->{Visible} = 1;
my $workbook = $excel->Workbooks->Open($file_name);
my $sheet = $workbook->Worksheets('Sheet 1');
foreach my $link (in $sheet->Hyperlinks ) {
say $link->Address;
}

But this gives code the error:

Win32::OLE(0.1709): GetOleEnumObject() Not a Win32::OLE::Enum object at C:/Dwimperl/perl/vendor/lib/Win32/OLE/Lite.pm line 167. Can't call method "Hyperlinks" without a package or object reference at at script.pl line 14.

It's selecting the right worksheet, so I am not sure why it complains about an object reference. I tried several variations (Adding {} around Hyperlinks, removing the 'in', trying to store it as a list, as a hash, and as a reference to a hash) Can anyone give me some pointers? Thanks!

Answer 1

First, you should set $Win32::OLE::Warn=3 so your script will croak the moment something goes wrong. Second, I know you can't select sheets by name in older versions of Excel, although I do not know what things are like in the newest versions. Finally, I think you'll find it easier to use Win32::OLE::Enum.

Here is an example:

#!/usr/bin/env perl

use 5.014;
use warnings; use strict;

use Carp qw( croak );
use Path::Class;
use Try::Tiny;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Enum;
$Win32::OLE::Warn = 3;

my $book_file = file($ENV{TEMP}, 'test.xls');
say $book_file;

my $excel = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;});
$excel->{Visible} = 1;

my $book = $excel->Workbooks->Open("$book_file");
my $sheet = get_sheet($book, 'Sheet with Hyperlinks');
my $links = $sheet->Hyperlinks;
my $it = Win32::OLE::Enum->new($links);

while (defined(my $link = $it->Next)) {
    my $address = $link->{Address};
    say $address;
    if ($address =~ s/example/not.example/) {
        $link->{Address} = $address;
        $link->{TextToDisplay} = "Changed to $address";
    }
}

$book->Save;
$book->Close;
$excel->Quit;

sub get_sheet {
    my ($book, $wanted_sheet) = @_;

    my $sheets = $book->Worksheets;
    my $it = Win32::OLE::Enum->new($sheets);

    while (defined(my $sheet = $it->Next)) {
        my $name = $sheet->{Name};
        say $name;
        if ($name eq $wanted_sheet) {
            return $sheet;
        }
    }

    croak "Could not find '$wanted_sheet'";
}

The workbook did contain a sheet with the name "Sheet with Hyperlinks". Cell A1 in that sheet contained http://example.com and A2 contained http://stackoverflow.com.