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

Answer 1

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

Answer 2

I think you're on the right track within Win32::OLE. I've used it to automate working with Excel in the past. I don't believe that OLE gives you access to GUI elements (e.g. activating a button press) so you'll probably need to find out whether or not the toolbar supports OLE and what the interface is.

The other alternative I can think of would be to try programmatically control the mouse to actually click the button. That assumes that you're running OLE with Excel visible (it doesn't have to be) and creates the difficult situation of figuring out how to position the cursor. OLE would be much simpler if it's an option.

Answer 3

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