That's a good idea. It would have the downside that you'd have to flag the other process when you're done writing, and I think you'd have some headaches there. You'd probably have to introduce a waiting period - like, sleep for a second, check for file, read it, write out results, etc. Those seconds can add up.
Fortunately, I finally sat down in front of Excel and here's what I whipped up. It actually works, which stunned me.
First, in your VBA project, go to Tools->References, find 'Windows Script Host Model', and check the box.
Next add this VBA code, or something like it:
Option Explicit
Dim perlProc As WshExec
Dim perlIn As TextStream
Dim perlOut As TextStream
Private Sub Workbook_Open()
Dim wsh As New WshShell
Set perlProc = wsh.Exec("C:\perl\bin\perl C:\perl\play\wordnet-ipc.p
+l ")
Set perlIn = perlProc.StdIn
Set perlOut = perlProc.StdOut
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
perlProc.Terminate
End Sub
This handles starting and stopping the wordnet-ipc script, which can basically be as above. You should see a DOS box appear when you open the worksheet, if it's working right.
You could add a sub to restart the perl process if it dies.
Next step is to hook in your functions in VBA....
Function doQueryWord(aWord As String, aRole as String) As String
' Construct a command in the simple protocol wordnet-ipc expects
Dim cmd as String
cmd = "word" & ";" & aWord & ";" & aRole
' Send the command to the waiting perl script
perlIn.writeLine cmd
' Read the output from the perl script. This will block until outpu
+t appears.
Dim results As String
results = perlOut.readLine
' Return results. You could split on commas - not sure what format
+you need.
doQueryWord = results
End Function
' doQuerySense would be nearly identical
That should do the trick. I haven't tried it with WordNet, as I don't have it installed on my windows box, but I did write a simple Perl print-execute-wait loop, and it worked fine.
best of luck!
--Clinton
|