How can I activate an Excel add-in from Perl or the command line?

Question

(Please forgive my ignorance on Excel add-ins, and feel free to correct my teminology where appropriate.)

I have an Excel add-in that is used regularly. This add-in inserts a toolbar with a number of buttons. I want to automate the task of opening a spreadsheet in Excel and then "clicking" one of those buttons. In other words, I want to use Perl (or the command line) to activate a particular function of this add-in.

I do not have immediate access to the source code for the add-in, but I should be able to request specific information such as procedure names if required.

I cannot use CPAN modules for this task—only what is installed with my version of ActivePerl—but I do have Win32::OLE, which has been helpful for other Office automation.

Any pointers?

Answer 1

Is there a key binding for the toolbar button?

If there is, you could use the SendKeys method to send that key to Excel: http://msdn.microsoft.com/en-us/library/aa202943(office.10).aspx

Alternatively, the CommandBars collection might be useful. See http://msdn.microsoft.com/en-us/library/aa171356(office.11).aspx for reference.

The sample code below lists the visible command bars and the controls in the 'Standard' toolbar. When it finds a control with the caption Open, it invokes the control. This should display the "File -> Open" dialog:

#!/usr/bin/perl

use strict;
use warnings;

use Win32::OLE qw(in with);
$Win32::OLE::Warn = 3;

my $app = get_excel();
$app->{Visible} = 1;

my $book = $app->Workbooks->Add;

for my $bar (in $app->CommandBars) {
    if ( $bar->{Visible} ) {
        print $bar->{Name}, "\n";
    }
}

print "Controls in the 'Standard' toolbar:\n";

my $bar = $app->CommandBars->{Standard};
for my $control (in $bar->{Controls}) {
    print "\t", $control->{Caption}, "\n";
    if ( $control->{Caption} =~ /^Open/ ) {
        print "opening ...\n";
        $control->Execute;
    }
}

sub get_excel {
    my $excel;
    eval {
        $excel = Win32::OLE->GetActiveObject('Excel.Application');
    };

    die "$@\n" if $@;

    unless(defined $excel) {
        $excel = Win32::OLE->new('Excel.Application', sub { $_[0]->Quit })
            or die "Oops, cannot start Excel: ",
                   Win32::OLE->LastError, "\n";
    }
    return $excel;
}

__END__

HTH

Answer 2

I don't know how you would go about clicking one of those buttons.
But I might have a workaround. If you can create a macro in excel to press the button calling that macro from perl is possible.

Untested!

#!c:\perl\bin\
use strict;

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 $excelfile = $path_to_exelfile_with_macro;

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
        || Win32::OLE->new('Excel.Application', 'Quit');

my $Book = $Excel->Workbooks->Open($excelfile);

$Excel->Run($MacroName);

More tips at http://www.perlmonks.org/?node_id=153486