#!/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 l
+ine
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 per
+l.

=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 i
+nto 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 edite
+d 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 wit
+hout 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
[download]