sourcecode
Mr. Muskrat
<code>
#!/usr/bin/perl -w
# vba2pl version 0.5, 2002-05-09
use strict;
use warnings;
use Getopt::Long;
use Pod::Usage;
use File::Basename;
my $help = 0;
my $man = 0;
my $app = 'Excel'; # Defaults to 'Excel'
my $macro = ''; # No default... must be passed on the command line
GetOptions('help|?' => \$help, man => \$man, 'app=s' => \$app, 'macro=s' => \$macro) or pod2usage(2);
pod2usage(1) if $help;
pod2usage(-exitstatus => 0, -verbose => 2) if $man;
pod2usage(2) if ($macro eq '');
my ($base, $path, undef) = fileparse($macro,'\..*');
my $pl = "$path$base.pl";
my $code .= <<EndOfCode;
#!/perl/bin/perl
use strict;
use warnings;
use Win32::OLE;
use Win32::OLE qw(in valof with);
use Win32::OLE::Const 'Microsoft $app';
\$|++;
\$Win32::OLE::Warn = 3;
my \$$app;
eval {\$$app = Win32::OLE->GetActiveObject('$app.Application')};
die "$app not installed" if \$@;
unless (defined \$$app) {
\$$app = Win32::OLE->new('$app.Application', sub {\$_[0]->Quit;}) or die "Cannot start $app";
}
\$$app\->{Visible} = 1;
EndOfCode
my $with = 0;
open(BAS, "<", $macro);
foreach (<BAS>) {
/^\s*$/ && next;
s/'.*//;
s/\s*\n$/\n/;
s/\s*Attribute .*//i && next;
s/(\s*)Next(?:\s+\w+)?/$1}/i && next;
s/^(\s+)\./$1/;
s/"/'/g;
s/^\s+('.*)/$1/;
s/([^\d+])\./$1->/g;
s/&\s+(\w+)/. \$$1/g;
s/Application/\$$app/i;
s/_\n//;
s/(\s*)Set\s+/$1/i;
s/(\s*)For Each (\w+) In (.*)/$1foreach \$$2 ($3) {/i && next;
if (m/(\s*)For (\w+) = (\d+|\w+)\s+To\s+(\d+|\w+)(?:\s+Step\s+(\S+))?/) {
my ($cond, $newinc);
my ($ws, $var, $start, $end, $inc) = ($1, $2, $3, $4, $5);
$var = "\$$var";
if ($inc =~ s/-//) {
$cond = ">=";
} else {
$cond = "<=";
}
if ($inc == 1) {
if ($cond eq ">=") {
$newinc = "--$var";
}else{
$newinc = "++$var";
}
}else{
if ($cond eq ">=") {
$newinc = "$var - $inc";
}else{
$newinc = "$var + $inc";
}
}
$start = "\$$start" if ($start !~ /\d+/);
$end = "\$$end" if ($end !~ /\d+/);
$_ = $ws . "for (my $var = $start, $var $cond $end, $newinc) {";
next;
}
s/(\s)If (.*) Then/$1if ($2) {/i && next;
s/\s*Sub (.*)/sub $1 {/i && next;
if (s/(\s*)End With/$1);/i) { $with++; next; }
s/(\s*)End .*/$1}/i && next;
if (s/\s*With ((\w+)(->.*)*)/\$Range = \$$1;\nwith (\$Range,/i) { $with++; next; }
s/(\s*)Dim (\w+)(?: as .*)?/$1my \$$2;/i && next;
s/^\s*(\w+.*)/\$$1/ if (!($with % 2));
s/(\w+->\S+->)(\w+)(.*)/$1\{$2}$3/;
s/=/=>/ if ($with % 2);
$with % 2 ? s/(.*[^;|^,])\n/$1,\n/ : s/(.*[^;|^,])\n/$1;\n/;
}
continue {
$code .= $_;
}
close(BAS);
open(PL, ">", $pl) || die "Unable to create $pl, stopped $!";
print PL $code;
close(PL);
print "$pl created.\n";
__END__
=head1 NAME
B<vba2pl> - VBA to Perl
=head1 SYNOPSIS
vba2pl.pl -macro file [-app application]
vba2pl.pl -help
vba2pl.pl -man
=head1 OPTIONS
=over 4
=item B<-macro>
The mandatory filename of the VBA macro you wish to translate into perl.
=item B<-app>
The optional name of the MS application the macro was created in. This
will probably be mandatory in the near future. (Default: Excel)
=item B<-help>
Print a brief help message and exits.
=item B<-man>
Prints the manual page and exits.
=back
=head1 DESCRIPTION
B<vba2pl> will read the given input file and attempt to translate it into perl.
It creates a perl file with the same name as the VBA macro but with a .pl extension.
It is far from finished. There is much work to do.
=head1 BUGS
There are bound to be bugs at this point in the development cycle.
=head1 HISTORY
Version 0.1 -
First version. Creates a perl snippet for insertion into preexisting code. Based on
the snippet I posted at http://www.perlmonks.org.
Version 0.2 -
Now creates somewhat standalone perl code. It still needs to be edited but the shebang
and modules are added to the top as well as some variable declarations. Added pod.
Now uses Getopt::Long and Pod::Usage.
Version 0.3 -
Now uses File::Basename instead of a regex to extract the filename without the extension.
Version 0.4 -
Added some more regexes to handle if, for, end if, next, etc...
Version 0.5 -
Fixed the Next bug and For is handled better now.
=head1 AUTHOR
Written by Matthew Musgrove E<lt>muskrat@mindless.comE<gt>
=cut
</code>
<p>vba2pl reads a VBA macro file and attempts to translate the contents to perl. It outputs to a file with the same path and base name but a .pl extension.
<p>It's far from finished although it has come a long way since I started on it last night.
<p>It got its start in my [164449|follow up] to [164439|Win32 - M$ Outlook and Perl.]
<p>Mandatory "Dark Side" quotes...<br>
"If you only knew the power of the Dark Side of the Force" - Darth Vader<br>
"Once you start down the Dark Path, forever will it dominate your destiny, consume you it will..." - Yoda<br>
Utility Scripts
Matthew Musgrove<br>
muskrat@mindless.com