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

Answer 2

You cannot use .NET classes in a VBA macro.

Use the VBA Shell function.

Shell "p:\ath\to\perlscript.pl"

The documentation:

Shell Function

Runs an executable program and returns a Variant (Double) representing the program's task ID if successful, otherwise it returns zero.

Syntax

Shell(pathname[,windowstyle])

The Shell function syntax has these named arguments:

Part Description pathname Required; Variant (String). Name of the program to execute and any required arguments or command-line switches; may include directory or folder and drive. On the Macintosh, you can use the MacID function to specify an application's signature instead of its name. The following example uses the signature for Microsoft Word: Shell MacID("MSWD") windowstyle Optional. Variant (Integer) corresponding to the style of the window in which the program is to be run. If windowstyle is omitted, the program is started minimized with focus. On the Macintosh (System 7.0 or later), windowstyle only determines whether or not the application gets the focus when it is run.

The windowstyle named argument has these values:

Constant Value Description vbHide 0 Window is hidden and focus is passed to the hidden window. The vbHide constant is not applicable on Macintosh platforms. vbNormalFocus 1 Window has focus and is restored to its original size and position. vbMinimizedFocus 2 Window is displayed as an icon with focus. vbMaximizedFocus 3 Window is maximized with focus. vbNormalNoFocus 4 Window is restored to its most recent size and position. The currently active window remains active. vbMinimizedNoFocus 6 Window is displayed as an icon. The currently active window remains active.

Answer 3

ActiveState's PDK has PerlCtrl which lets you package a perl script as an ActiveX control. It gathers up your script and all dependencies into a tidy DLL.