Convert Perl Script to VBA Script

Question

I have a working Perl script that I would like to convert to VBA to run in an Excel macro so that it can easily be shared to other PC's. I have a shell script (where I pass the parameters) driving the perl script. I used the Perl script to read each specified column of all rows of data from a fixed width file (below the start is 54,63) and compare that data with another file and print the difference. I'd pass parameters in the Shell script as runpro.pl filea.txt fileb.csv > myoutput.txt Any assistance would be great! Especially if someone can point me in the right direction since the code is fairly simple. Thanks!

#!/usr/bin/perl
#Perl Script runpro.pl
#***************************************************

use strict;
use warnings;

my ($fa, $fb) = @ARGV;

@ARGV  = $fa;
my %codes;
while(<>) {
 s/[\r\n]+\z//;
 $_ = substr($_, 54, 63);
 s/\s+\z//;
 next if $_ eq "";
 $codes{$_} =1;
}

@ARGV  = $fb;
my %descrip;
while(<>) {
 s/[\r\n]+\z//;
 s/,.*//;
 s/"//g;
 $descrip {$_} = 1 if s/^1234//; 
}

for (sort keys %codes) {$   
print "$_\n" unless ($descrip{$_});
}

Can you give an example of how you would use a VBA function - how you pass parameters, and where you want output to appear? It's not entirely clear from your question... — Floris
– Floris, Commented Mar 6, 2013 at 20:23
Check this: docs.activestate.com/activeperl/5.6/faq/Windows/… "If you record a macro in Microsoft Office, this can often be translated directly into Perl." — isomorphismes
– isomorphismes, Commented Mar 21, 2013 at 23:29

Community · Accepted Answer · 2017-05-23 12:11:05Z

2

A couple of points:

1) VBA is very different from Perl - so things that are one-liners in one will be tricky in the other

2) If you haven't used VBA in Excel, I suggest you start by "recording" a macro (first make the "Developer" tab in the ribbon visible, then select "record macro", and start doing things like opening files, importing them (fixed width). After you stop the recording you will see the syntax for doing these things - that should help a lot

3) You will have to decide how you want to pass arguments to VBA - cells on a worksheet, dialog box... There is no such thing as "running VBA from the command line".

I wonder if you really need / want VBA or if you would be better off compiling a standalone program (.exe). Is this meant to run on PC (windows), Mac OS, or both? See for example this earlier question - maybe that's what you actually need (if not what you asked for...)?

edited May 23, 2017 at 12:11

CommunityBot

11 silver badge

answered Mar 7, 2013 at 5:08

Floris

46.6k7 gold badges73 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert Perl Script to VBA Script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related