Thursday, April 26, 2007

Ruby & Excel: The InputBox Hack

A ruby-talk (comp.lang.ruby) reader asked how to get user input on Windows without resorting to console input.

While you can use Windows API calls to create a Yes/No message box, there doesn't appear to be a simple API means for creating a dialog for accepting user string input.

As others on the thread have mentioned, you can use one of the GUI toolkits (ie, wxRuby) and, if you have other GUI needs, this is the best option. If, however, the only GUI need you have is for a single input dialog, you could use the InputBox function from Excel, via the win32ole library (assuming Excel is installed):


require 'win32ole'

def get_input(prompt='', title='')
excel = WIN32OLE.new('Excel.Application')
response = excel.InputBox(prompt, title)
excel.Quit
excel = nil
return response
end

response = get_input('My Prompt', 'My Title')

Granted, this is somewhat of a hack if you're loading Excel into memory merely to display an input dialog. On the other hand, it may be simpler than adding an entire GUI library to your project, especially if you'll eventually be wrapping up all those dependencies into a distributed package, a la rubyscript2exe.

5 comments:

tremendo said...

I say it would be better to use IE's script engine (input, confirm, alerts), you get more options, and IE is guaranteed to be present on a Windows machine, whereas Excel isn't.
js = ie.document.script
js.alert(prompt)
r = js.confirm(prompt)
r = js.input(prompt)
...

Anonymous said...

js.input(prompt) results in an error. unknown property or method `input' (WIN32OLERuntimeError). What could be wrong?

Anonymous said...

r = js.input(prompt) should be
r = js.prompt(prompt)

steenslag said...

This is a another possibility:

def inputbox( message, title="Message from #{__FILE__}" )
# returns nil if 'cancel' is clicked
# returns a (possibly empty) string otherwise
require 'win32ole'
# hammer the arguments to vb-script style
vb_msg = %Q| "#{message.gsub("\n",'"& vbcrlf &"')}"|
vb_msg.gsub!( "\t", '"& vbtab &"' )
vb_msg.gsub!( '&""&','&' )
vb_title = %Q|"#{title}"|
# go!
sc = WIN32OLE.new( "ScriptControl" )
sc.language = "VBScript"
sc.eval(%Q|Inputbox(#{vb_msg}, #{vb_title})|)
end

def popup(message)
require 'win32ole'
wsh = WIN32OLE.new('WScript.Shell')
wsh.popup(message, 0, __FILE__)
end

#simple use
res = inputbox "Your input please."
popup res

#using linebreaks and tabs
str = "a | does not break it...\n\nOne\n\tTwo tabbed\nThree..."
res = inputbox( str, "demonstration | title")
popup %Q|When asked\n\n"#{str}"\n\nyou answered:\n#{res}|

(code also available on http://snippets.dzone.com/posts/show/10261 .

hth,
Siep

Luis Cervantes said...

First at all, I give you an apologize... mi english is not so good!

What I have todo if I want a inputbox to select a range? Because en Excel I can do this:

set rngRange = Application.Inputbox("Select data", "Sample", type:=8)

With this rngRange is linked with the range seleccionated, but Ruby code dont work!

Help!