How do I call a Perl script from Excel?

Question

I want to call a Perl script from Excel using VBA. I have used a shell command. I have also used the code below:

Function Chk()
Dim RetVal
RetVal = Shell("C:\Perl\bin\Hello.pl", 1)
End Function

But the above function doesnt run the file. Is there any error?

Is there any other way to call a Perl script or external program using Excel?

Answer 1

You'll need to shell out to:

 c:\perl\bin\perl.exe Hello.pl

to make it run.

Answer 2

You could also add this line as the first in your script:

#!c:\perl\bin\perl.exe

Answer 3

Have a look at ActiveState's Perl extensions. They have an "ActivePerl" DLL which runs the Perl interpreter as an ActiveX component which should allow seamless integration with Excel.

In addition, their OLE extensions allow Perl to access the full Excel OLE Object model so you can manipulate the spreadsheet very precisely.

Answer 4

Excel's Shell() function is probably ignoring file associations. You almost certainly need to say something more like:

Function Chk()
  Dim RetVal
  Chk = Shell("C:\Perl\bin\perl.exe C:\Perl\bin\Hello.pl", 1)
End Function

to get the effect you wanted. The two changes to note are first, the explicit invocation of perl.exe, and second, the minor syntax error in your function as written.

Note that if either the path to perl.exe or the path to your script (or any additional arguments to your script too) has spaces in it, then you will need to form a string that contains double-quote characters quoting them, something like:

Function Chk()
  Dim RetVal
  Chk = Shell("C:\Perl\bin\perl.exe ""C:\Some path\with spaces\Hello.pl""", 1)
End Function

would do the trick.