How can I run a Perl script through an ActiveX Control within Excel?

Question

I want to run a Perl script at the click of a button inside an Excel spreadsheet.

As the button is assigned to execute a VB macro, the macro should effectively execute the program.

As my first ever VB script, this is what I came up with, which throws up an irritating Run-time error '424': Object required error.

Sub RunPerlScript()
    System.Diagnostics.process.Start ("perlscript.pl")
End Sub

How can I get this script to do what I want it to do?

Answer 1

I didn't write this snippet, but it would seem to be a good answer to your question.

From the article "How to execute a perl script from VBA":

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

You might need to install ActivePerl first