James Britt
Style
Default
Big type
Ruby and VBA for Web Dictionary Searches
I’m coauthoring a book about the Ruby programming language (Beginning Ruby Programming, from Wrox Ltd.), and use Microsoft Word on Windows 2000. Over the years I’ve spent a fair amount of time writing VBA macros for Word, even going so far as to write a suite of classes for exporting Word docs as XML. I like having common routines automated, and have acquired a number of macros. Typically, I use the macro recorder to capture the general behavior, and then use the VBA IDE to tweak the results.
VBA is essentially Visual Basic, coupled with an IDE, and built into various Windows (and Office for Macintosh) applications. I like it well enough, having spent much time writing Visual Basic programs. You can call Win32 libraries, or script ActiveX DLLs, as well as manipulate office applications. However, I would now much prefer to write the macros in Ruby.
There is a Ruby application (http://www.pso-world.com/viewtopic.php?topic=78520&forum=14) called RubyCOM that exposes Ruby classes as COM automation objects, and I tried using that inside of VBA. Unfortunately, I ran into some problems I’ve yet to solve. However, there is another way to exploit Ruby inside of Word macros, and that’s through the use of the Shell function. This is perhaps not quite graceful; you write some or most of the code in a Ruby script, and call the script using Shell, much as you would call it from the command line. Shell does not let you grab the output. You need to redirect text to a temporary files, and have the VBA code slurp it in. Still, if you can live with some degree of hackishness, you can make good use of Ruby.
Microsoft Word has a spelling checker, and a thesaurus, but it does not have a dictionary. It would be nice to be able to highlight a word and find out the meaning. There are a few web sites offering free dictionary services, such as dictionary.com and m-w.com. Ralph Mason, the author of RubyCOM, wrote a Ruby console app that takes a word from the command line, calls out to dictionary.com for the definition, strips the HTML from the results, and displays the final text in the console window. Very handy. I decided I wanted to be able to call this from Word, so I set out to write a VBA wrapper routine.
Spawning an application from VBA is simple. You use Shell, like this:
Shell cmd, window_style
To execute a Ruby script, you might do this:
Shell "cmd /C somescript.rb", vbHide
This actually calls the command interpreter (cmd.exe), passing it the name of the Ruby script. The second parameter to Shell says to hide the console window (as oppose to, say, simply minimizing it).
The /C switch tells the interpreter to execute the command, and then quit. If you wanted to keep the command interpreter running, you would use /K. (You can get full list of switch by running cmd /?) All well and good, calling a Ruby script from VBA is no problem. Just pass the name of the script, use output redirection if needed, and have the VBA code grab the results.
Well, this is almost correct. The problem is that Shell executes asynchronously. Hence, if you have subsequent code relying on the results of the spawned script, there is no guarantee the Ruby code will have completed by the time the remaining VBA code runs. I tried a few things myself, little loops that checked for file existence or error codes, but found a much better solution on the MSDN (Microsoft Developer Network) web site. There is an article, “HOWTO: 32-Bit App Can Determine When a Shelled Process Ends”, that explained how to track a processes ID (available as the return value from Shell), and gives complete code for a better Shell function. The better Shell, called ExecCmd, takes the name of a shell command, but does not return until the called process is done. Presto: synchronous code.
Here is the code, taken from that article. All copyrights for this part remain with Microsoft, and I’m presenting it here only for convenience.
' Code taken from the MSDN article, 'HOWTO: 32-Bit App Can Determine When
' a Shelled Process Ends'
' Microsoft Knowledge Base Article - Q129796
' http://support.microsoft.com/default.aspx?scid=kb;[LN];Q129796
Private Type STARTUPINFO
cb As Long
lpReserved As String
lpDesktop As String
lpTitle As String
dwX As Long
dwY As Long
dwXSize As Long
dwYSize As Long
dwXCountChars As Long
dwYCountChars As Long
dwFillAttribute As Long
dwFlags As Long
wShowWindow As Integer
cbReserved2 As Integer
lpReserved2 As Long
hStdInput As Long
hStdOutput As Long
hStdError As Long
End Type
Private Type PROCESS_INFORMATION
hProcess As Long
hThread As Long
dwProcessID As Long
dwThreadID As Long
End Type
Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long
Private Declare Function CreateProcessA Lib "kernel32" (ByVal lpApplicationName As String, ByVal lpCommandLine As String, ByVal lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As String, lpStartupInfo As STARTUPINFO, lpProcessInformation As PROCESS_INFORMATION) As Long
Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long
Private Const NORMAL_PRIORITY_CLASS = &H20&
Private Const INFINITE = -1&
The original code had various line breaks, which didn’t travel well when I did a cut-n-paste. I removed them, so the code here may have bad line wrapping. I’m not going to explain this code, other than to say it declares the use of various Win32 library functions for tracking a process. Read the MSDN article for more details. However, you don’t need to understand it to use it.
The next part is the better Shell:
' Code taken from the MSDN article, 'HOWTO: 32-Bit App Can Determine When
' a Shelled Process Ends'
' Microsoft Knowledge Base Article - Q129796
' http://support.microsoft.com/default.aspx?scid=kb;[LN];Q129796
Public Function ExecCmd(cmdline$)
Dim proc As PROCESS_INFORMATION
Dim start As STARTUPINFO
' Initialize the STARTUPINFO structure:
start.cb = Len(start)
' Start the shelled application:
ret& = CreateProcessA(vbNullString, cmdline$, 0&, 0&, 1&, NORMAL_PRIORITY_CLASS, 0&, vbNullString, start, proc)
' Wait for the shelled application to finish:
ret& = WaitForSingleObject(proc.hProcess, INFINITE)
Call GetExitCodeProcess(proc.hProcess, ret&)
Call CloseHandle(proc.hThread)
Call CloseHandle(proc.hProcess)
ExecCmd = ret&
End Function
Good. Now comes the Dictionary routine, which calls a Ruby script and displays the results in a user form text box. (You’ll need to create a form, with a text box, for this macro to work. The user form is named user_form; the text box is named text_box. Clever, no? Set the text box to multi-line, with vertical scrolling. I also set the border to single-width, since I make no provisions for form resizing. The caption is set by the macro, so it doesn’t really matter what it is in the IDE.)
The subroutine itself is pretty sparse:
Sub Dictionary()
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim def As String
Dim cmd As String
Dim word As String
Dim temp_file As String
The sub starts by defining a few constants and variables. The output of the Ruby script will go into a file in the system temp directory; the Environ method lets the code look up environment variables, in this case use TEMP. You may prefer to have the output text go someplace else, but in any case using an environment variable is a good way to keep the code portable.
Set fs = CreateObject("Scripting.FileSystemObject")
def = ""
temp_file = Environ("TEMP") & "dict.tmp"
If there is an existing temp file, it gets deleted:
FindIt = Dir(temp_file)
If Not Len(FindIt) = 0 Then
Kill temp_file
End If
(This is one of those places I start thinking, Oh how simpler the Ruby code would be.) A command string is built from the selected text and the name of the temp file; the string is passed to ExecCmd for execution:
word = Selection.Text
cmd = "cmd /C dictionary.rb " & word & " > " & temp_file
ExecCmd (cmd)
Note that the code does not use a fully qualified path to the Ruby script. That’s because I’ve previously defined a particular directory on my machine for scripts, and have added this to my PATH environment variable. Any script in that directory can be called from any command prompt as if it were a native Windows command. If you don’t do this then you’ll need to provide a full path to the Ruby file.
Next, the output file is is read into a local variable:
Set ts = fs.OpenTextFile(temp_file, ForReading)
Do While ts.AtEndOfStream <> True
def = def & ts.ReadLine & vbLf
Loop
ts.Close
Finally, the form is displayed. The caption is set, the text loaded, and the form is made visible. The selection point set to the beginning of the text box in order to scroll the text back to the top. Without this, the form displays with the text scrolled to the very end, and you have to manually scroll to the top to read it.
user_form.Caption = word
user_form.text_box.Text = ReplaceEntities(def)
user_form.Show
user_form.text_box.SelStart = 0
End Sub
The call to ReplaceEntities swaps out certain HTML character entities in favor of the actual characters. You might want to add to this as you encounter additional characters, or change the Ruby script to do more HTML cleansing. But there may be a difference in what the console window can display, and what Word renders.
Function ReplaceEntities(str As String) As String
str = Replace(str, "·", "·")
str = Replace(str, "&", "&")
ReplaceEntities = str
End Function
Ralph Mason wrote the original Ruby code that does the useful part. I reorganized the code to provide a class one could reuse in other programs. WebDictionary does a lookup on http://www.dictionary.com. The results are reformatted and returned as plain text.
#!/usr/local/bin/ruby
#--------------------------------------------------------------
# Script to fetch a definition for dictionary.com, strip the HTML, and display
# the results in the console window. Original code written by Ralph Mason:
# http://www.geocities.com/masonralph/ruby.html
#
# Reorganized by James Britt
# http://www.jamesbritt.com
#--------------------------------------------------------------
require 'net/http'
class WebDictionary
def look_up( word )
site = "www.dictionary.com"
page = "/cgi-bin/dict.pl?term=#{word}"
a = Net::HTTP.get( site , page )
res = ""
if ( a=~/No entry found for/ )
puts "No entry found for '#{word}' - Suggestions:nn"
while $' =~/<a href="/cgi-bin/dict.pl?term=w+">(w+)/
res << $1
end
else
while a =~ //
$' =~ //
a=$'
res << $`.gsub(/<.*?>/m){ |i|
case i
when ""
""
when ""
"n"
when "
"
""
else
""
end
}
end
end
res
end
end
if __FILE__ == $0
if ARGV.size == 0
puts "Usage: #{$0} <some word> "
exit
end
puts WebDictionary.new().look_up( ARGV[0] )
end
That’s it. I put this code inside of my normal.dot template, and assigned a keyboard combination to the macro. I can now highlight a word, press CTRL+ALT+W, and get a pop-up window with the definition:
Enjoy!
James Britt
References
Ralph Mason's Ruby dictionary script: http://www.geocities.com/masonralph/ruby.html
Microsoft Knowledge Base Article - Q129796 HOWTO: 32-Bit App Can Determine When a Shelled Process Ends
Download the code: RubyVba.zip
Disclaimer: I’ve presented this article and code for informational purposes only, and make no claims for its correctness or suitability for any tasks whatsoever. Use this information at your own risk. The article text is owned and copyrighted 2002 by James Britt, and may not be reproduced without permission., The Dictionary and ReplaceEntities VBA macro are copyrighted 2002 by James Brit, and released for public use under the GPL. All other code is owned and copyrighted as noted, and all rights remain with the original creators.
<font size=-1>[ This Message was edited by: pixelate on 2003-09-25 21:27 ]</font>