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

Question

(Please forgive my ignorance of the Excel add-ons and feel free to fix my theme if necessary).

I have an Excel add-in that is used regularly. This add-in inserts a toolbar with several buttons. I want to automate the task of opening a spreadsheet in Excel, and then “click” one of these buttons. In other words, I want to use Perl (or the command line) to activate the specific function of this add-in.

I do not have direct access to the source code of the add-in, but I should be able to request certain information, such as procedure names, if necessary.

I cannot use CPAN modules for this task - only what is installed with my version of ActivePerl & mdash, but I have Win32 :: OLE , which was useful for other Office automation.

Any pointers?

Answer 1

Is there a key binding for the toolbar button?

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

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

The following code example lists the visible toolbars and controls on the Standard toolbar. When it finds a control labeled Open, it calls 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__ 

NTN

Answer 2

I do not know how you could click on one of these buttons.
But I can have a workaround. If you can create a macro in excel, click the button that invokes the macro from perl.

Not verified!

 #!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); 

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

Answer 3

I think you are on the right track in Win32 :: OLE. I used it to automate work with Excel in the past. I don’t think that OLE gives you access to GUI elements (for example, activates a button click), so you will probably need to find out if the toolbar supports OLE and what the interface is.

Another option I can think of would be to try to programmatically control the mouse in order to actually press a button. This assumes that you are using OLE with visible Excel (this is not necessary) and creates a difficult situation when you decide how to position the cursor. OLE will be much easier if this is an option.