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... |
#!/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 |
|
---|
Replies are listed 'Best First'. | |
---|---|
Re: VBA 2 Perl
by Mr. Muskrat (Canon) on May 08, 2002 at 22:17 UTC | |
Re: VBA 2 Perl
by Mr. Muskrat (Canon) on May 09, 2002 at 21:24 UTC | |
by Mr. Muskrat (Canon) on May 10, 2002 at 17:56 UTC |
Back to
Code Catacombs