Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

VBA 2 Perl

by Mr. Muskrat (Abbot)
on May 08, 2002 at 18:37 UTC ( #165127=sourcecode: print w/ replies, xml ) Need Help??

Category: Utility Scripts
Author/Contact Info Matthew Musgrove
muskrat@mindless.com
Description:

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.

It's far from finished although it has come a long way since I started on it last night.

It got its start in my follow up to Win32 - M$ Outlook and Perl.

Mandatory "Dark Side" quotes...
"If you only knew the power of the Dark Side of the Force" - Darth Vader
"Once you start down the Dark Path, forever will it dominate your destiny, consume you it will..." - Yoda

#!/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

Comment on VBA 2 Perl
Download Code
Re: VBA 2 Perl
by Mr. Muskrat (Abbot) on May 08, 2002 at 22:17 UTC
    For historical archiving I give you the original
    Version 0.1 code follows:
    #!/usr/bin/perl -w # vba2pl version 0.1 my $app = shift; # I know... next version will GetOpts :) my $macro = shift; my ($name, $ext) = split('.',$macro); # I know... next version will + use file::basename my $pl = "$name.pl"; my $code = ""; open(BAS, "<", $macro); while (<BAS>) { s/\./->/g; s/=/=>/; s/"/'/g; s/\s+End With/);/; s/\s+With (\w+)->(\w+)/\$Range = \$$1->$2;\nwith (\$Range/i; s/Application/\$$app/i; s/(.*[^;])\n/$1,\n/; $code .= $_; } close(BAS); open(PL, ">", $pl); print PL $code; close(PL);

    Who says that programmers can't work in the Marketing Department?
    Or is that who says that Marketing people can't program?
Re: VBA 2 Perl
by Mr. Muskrat (Abbot) on May 09, 2002 at 21:24 UTC

    Okay, version 0.5 is posted now.
    I fixed a bug involving 'Next' and redid the 'For' code.

    I'm looking into Parse::RecDescent for a later version.
    Thanks jeffa!

    Note to self... it needs more comments!


    Who says that programmers can't work in the Marketing Department?
    Or is that who says that Marketing people can't program?

      Parse::RecDescent?!?!!!
      What was I thinking? It was written by TheDamian!!!!!
      I'll never understand it! :)

      Wait! What's this? A tutorial?
      And Merlyn has a column in which he talks about it?
      And Craig Smith has a tutorial as well?
      The list seems to go on and on... so perhaps given sufficient time...
      We will just have to wait and see if I can grasp it.


      Who says that programmers can't work in the Marketing Department?
      Or is that who says that Marketing people can't program?

Back to Code Catacombs

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: sourcecode [id://165127]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (4)
As of 2014-09-15 05:39 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite cookbook is:










    Results (145 votes), past polls