Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical

Access PerlScript example

by ichimunki (Priest)
on Aug 01, 2002 at 17:17 UTC ( #186849=note: print w/replies, xml ) Need Help??

in reply to (Win32): Excel PerlScript example

Just for fun (and because I might be able to use this to save my keister someday) I extended this to run a Perl script contained wholly in a field in an MS Access table (and therefore as part of an Access "application"). For my example, the table is simply 'perl_scripts', with fields 'script_name' (text field), 'script' (memo field). I then built a form with a really big box for the script memo field and that becomes a mini-editor (I'm sure with proper patience one could work that form field into a decent enough text editor-- but more importantly this allows a cut & paste of code from a real editor, like emacs).

Function run_script(q As String) As String ' To use this sub/function you must have ' Perlscript installed* and then register the ' Microsoft Script Control 1.0 (MSSCRIPT.OCX) ' which you must register from the Tools-References ' menu of the VBA editor. ' * standard in ActiveState's ActivePerl distribution 'requires table named "perl_scripts" with fields 'script_name (text) 'script (memo) On Error GoTo run_script_error Dim db As Database Set db = CurrentDb Dim scripts As Recordset Set scripts = db.OpenRecordset("perl_scripts", dbOpenSnapshot) Dim finder As String finder = "script_name='" & q & "'" scripts.MoveFirst scripts.FindFirst (finder) If scripts.NoMatch Then Debug.Print "Script " & q & " not found." Err.Raise 448 End If Dim ps As String ps = scripts("script") Dim perl As New ScriptControl perl.Language = "PerlScript" run_script = perl.Eval(ps) Exit Function run_script_error: MsgBox ("Error running Perl script: " + Error$) run_script = False End Function
Here is the sample Perl code I ran. Note that it allows strict, and at this point does not accept any input from the VBA code. I think to do that the best way would be to run perl.ExecuteStatement("$globalSetting = '" & theSetting & "'"'s before the perl.Eval command. But then we might have to turn off strict. Also note that this code is required to return a value as it's last statement (and that VBA treats it as a string).
use strict; sub foo { my $name = shift; return "Hello, $name.\n"; } sub bar { my $name = shift; return "Good-bye, $name!\n"; } my $return = foo("World") . bar("Cruel World"); return $return;
We can then run this function with a quick routine like:
Sub test_run_script() Debug.Print run_script("Perl Test") End Sub

Either way, thanks for the heads up on the ability to run Perl from VBA. This is what I've been saying would make my life easier for a long time... next to figure out if there's a good way to get Perl talking to Access/VBA objects themselves and to return more than a simple scalar. Any idea if/where The Fine Manuals on this would be? I'll be looking myself and posting links if I find anything.

Replies are listed 'Best First'.
Re: Access PerlScript example
by osfameron (Hermit) on Aug 02, 2002 at 10:21 UTC
    Very nice!

    next to figure out if there's a good way to get Perl talking to Access/VBA objects themselves and to return more than a simple scalar.
    I've not used Win32::OLE to connect to Access. Actually, I barely use Access as I find it very confusing, so I don't know if it works. But I've had some success using Win32::OLE to connect to Excel and Outlook and return real COM objects.


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://186849]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (5)
As of 2020-10-28 11:42 GMT
Find Nodes?
    Voting Booth?
    My favourite web site is:

    Results (260 votes). Check out past polls.