How can I call a Perl script from Excel?

Question

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

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

But the above function does not run the file. Is there a mistake?

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

Answer 1

Check out the ActiveState Perl extensions. They have an "ActivePerl" DLL that runs the Perl interpreter as an ActiveX component that should provide seamless integration with Excel.

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

Answer 2

You need to execute the command:

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

to run it.

Answer 3

You can also add this line first in the script:

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

Answer 4

The Excel Shell () function probably ignores file associations. You almost certainly have to say something more:

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

to get the desired effect. Two changes that need to be noted are, firstly, an explicit call to perl.exe, and secondly, a minor syntax error in your function, as it is written.

Please note that if the path to perl.exe or the path to your script (or any additional arguments for your script too) have spaces in it, then you will need to create a line, quote them, quoting them:

 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.