Write an Excel Macro in Perl

Basilides has asked for the wisdom of the Perl Monks concerning the following question:

I thought jmcnamara's example in this node was really interesting, but it set me thinking: can you create a spreadsheet complete with a button linked to a macro from Perl?

So you create your button withmy $button = $worksheet->Buttons->Add(71.25, 18.75, 88.5, 57); but the Workbook needs a VBA module with a subroutine in it, so that you can link it to the button's OnAction property, right?

Just asking out of curiosity, but is it possible?

Comment on Write an Excel Macro in Perl Select or Download Code

Replies are listed 'Best First'.
Re: Write an Excel Macro in Perl by jsprat (Curate) on Nov 06, 2002 at 21:41 UTC
Anything's possible with a little help from OLE (and an hour to kill reading Excel docs;-) #!/usr/bin/perl use strict; use warnings; use Win32::OLE; use Win32::OLE::Const "Microsoft Excel"; use Win32::OLE::Const "Microsoft Visual Basic for Applications Extensi +bility"; my $xls = Win32::OLE->new('Excel.Application'); $xls->{Visible} = -1; my $wb = $xls->Workbooks->Add(); my $mod = $wb->VBProject->VBComponents->Add(vbextFileTypeModule); $mod->CodeModule->AddFromString ( <<MODTEXT); Sub Message dim s as string s = "Hello, World" MsgBox s End Sub MODTEXT my $sheet = $xls->Sheets('Sheet1'); my $button = $sheet->Buttons->Add(71.25, 18.75, 88.5, 57); $button->{OnAction} = 'Message'; [download] Notes: Error checking is left as an exercise. I tested this with Excel 97. VBProject objects are horribly documented in the Excel helpfiles. I ended up using the object browser from ActiveState to put it all together.	[reply] [d/l]

Replies are listed 'Best First'.

Re: Write an Excel Macro in Perl
by jsprat (Curate) on Nov 06, 2002 at 21:41 UTC

#!/usr/bin/perl

use strict;
use warnings;
use Win32::OLE;
use Win32::OLE::Const "Microsoft Excel";
use Win32::OLE::Const "Microsoft Visual Basic for Applications Extensi
+bility";

my $xls = Win32::OLE->new('Excel.Application');
$xls->{Visible} = -1;
my $wb = $xls->Workbooks->Add();
my $mod = $wb->VBProject->VBComponents->Add(vbextFileTypeModule);
$mod->CodeModule->AddFromString ( <<MODTEXT);
Sub Message
    dim s as string
    s = "Hello, World"
    MsgBox s
End Sub
MODTEXT

my $sheet = $xls->Sheets('Sheet1');
my $button = $sheet->Buttons->Add(71.25, 18.75, 88.5, 57);
$button->{OnAction} = 'Message';
[download]

Notes:

Error checking is left as an exercise.
I tested this with Excel 97.
VBProject objects are horribly documented in the Excel helpfiles. I ended up using the object browser from ActiveState to put it all together.

[reply]
[d/l]