This short article explains how to execute a Perl script (or any other external program) from a VBA macro. You can use this method to provide a convenient way to collect parameters for your script. Simply draw a form in you VBA editor (using input fields for all parameters), then execute the script using the command line arguments.
First of all, you need a Perl script that does all the work. Here is the code for a very useful script:
print "Hello World from perl\nArgument:".$ARGV;
die "Error in Perl\n";
Save this script to C:\temp\myperl.pl. Sorry, the path is hard coded! The script prints something to STDOUT (
print) using the first command line argument. It also prints a value to STDERR (
die) that will be displayed in our Excel macro.
The macro code executes the external program (here, the Perl script), waits until the program ends, and displays the result, using a
MsgBox. To execute the external process, we need to create an object called
ll and use its
Exec method. The
WScript.Shell object is part of the Microsoft Scripting Runtime. This means we have to add a reference to this library (Extras -> Add-ins...).
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long
MsgBox ("Start of macro")
Dim oWsc As Object
Set oWsc = CreateObject("WScript.Shell")
Dim oExec As Object
Set oExec = oWsc.Exec("perl C:\temp\myperl.pl StartParam")
While oExec.Status <> 1 Sleep 1000
MsgBox ("STDOUT" + oExec.StdOut.ReadAll())
MsgBox ("STDERR" + oExec.StdErr.ReadAll())
Set oWsc = Nothing
MsgBox ("End of macro")
The other important thing: how to wait until the process ends? We declare the function
Sleep. This function is called as long as the process is running
While oExec.Status <> 1.
Using Excel forms to collect commandline arguments gives your user a convenient way to execute Perl scripts, without the need to open a command prompt and type something like this:
>perl myperl.pl param1=value1 param2=value2 ...