Click here to Skip to main content
15,867,756 members
Articles / Programming Languages / Perl

How to execute a Perl script from VBA

Rate me:
Please Sign up or sign in to vote.
4.44/5 (5 votes)
25 Sep 2006CPOL1 min read 75.4K   362   6   9
How to execute a Perl script from VBA.

Introduction

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.

The Script

First of all, you need a Perl script that does all the work. Here is the code for a very useful script:

PERL
print "Hello World from perl\nArgument[0]:".$ARGV[0];
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

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 WScript.Shell 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...).

VB
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long
Sub RunPerl()
    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 ' Wait for process
        Sleep 1000
    Wend
    MsgBox ("STDOUT" + oExec.StdOut.ReadAll())
    MsgBox ("STDERR" + oExec.StdErr.ReadAll())
    Set oWsc = Nothing
    MsgBox ("End of macro")
End Sub

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.

Why???

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 ...

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
Germany Germany
Florian works as consultant for change- and configuration management for about 7 years. In this environment he is often forced to work with unix, perl and shell scripts.

For more information about change- and configuration management (espacially Serena Dimensions) visit: www.venco.de

For video tutorials about asp.net, ajax, gridviews, ... (in german) visit: www.siore.com

Comments and Discussions

 
QuestionError: Project must be updated for use on 64 bit systems Pin
Member 1497412524-Oct-20 3:59
Member 1497412524-Oct-20 3:59 
QuestionQuestion-passing parameters using this code Pin
Member 1013270029-Jun-13 16:33
Member 1013270029-Jun-13 16:33 
GeneralNot able to run Perl script on Network Drive Pin
Prashant Pattnaik29-Aug-08 9:35
Prashant Pattnaik29-Aug-08 9:35 
GeneralRe: Not able to run Perl script on Network Drive Pin
Member 1015162811-Feb-15 5:08
Member 1015162811-Feb-15 5:08 
GeneralSupress the command / console window when using Pin
mathaus1018-Nov-07 16:16
mathaus1018-Nov-07 16:16 
GeneralRe: Supress the command / console window when using Pin
fstrahberger10-Jan-08 19:54
fstrahberger10-Jan-08 19:54 
QuestionWhy is location hard coded? Pin
nettist20-Oct-06 8:38
nettist20-Oct-06 8:38 
AnswerRe: Why is location hard coded? Pin
fstrahberger20-Oct-06 21:25
fstrahberger20-Oct-06 21:25 
GeneralRe: Why is location hard coded? Pin
nettist21-Oct-06 12:34
nettist21-Oct-06 12:34 
Ah!...thank you for this explanation...I misunderstood that part of the code.
I will amend my module accordingly....cheers, Pete

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.