Sunday, May 17, 2009

Handling WIN32OLE Events in Excel

Someone recently asked how to have Ruby react to events in Excel. Specifically, they were trying retrieve the contents of a row in a worksheet when it's selected.

The win32ole module provides a WIN32OLE_EVENT class that will allow you to execute a block of code when a specific event occurs.

To set the scene, let's use the WIN32OLE.connect() method to connect to an existing instance of Microsoft Excel and grab a reference to the currently active workbook:


require 'win32ole'
xl = WIN32OLE.connect('Excel.Application')
wb = xl.ActiveWorkbook

Next, we'll call the WIN32OLE_EVENT.new() method to create a new OLE event object. You pass this method an OLE object---our Workbook object---and the name of the event sink. In this instance, we want to use the WorkbookEvents sink:

ev = WIN32OLE_EVENT.new(wb, 'WorkbookEvents')

Once you have your event sink defined, you call its on_event() method to hook into a particular event and run a block of code when that event fires. In our scenario, we want to take action when the SheetSelectionChange event fires.

ev.on_event('SheetSelectionChange') do
range = xl.Selection
puts(range.Value)
STDOUT.flush()
end

The above block of code will execute when the user selects a range of cells, and will print out the array of values from the selected cells.

Finally, you need to start the event message loop to begin the event monitoring:

loop do
WIN32OLE_EVENT.message_loop
end

In the real world, we need a means to exit the message loop. Let's catch the BeforeClose event, which fires (of course) just prior to the workbook being closed:

ev.on_event('BeforeClose') do
exit_event_loop
end

Now, when the BeforeClose event fires, we'll have it call a new exit_event_loop() method, which sets a $LOOP value to false:

$LOOP = true

def exit_event_loop
$LOOP = false
end

Finally, we'll modify our earlier message loop block, accordingly, and also toss in a brief pause:

while $LOOP
WIN32OLE_EVENT.message_loop
sleep(0.1)
end

Our complete code looks something like this:

require 'win32ole'

xl = WIN32OLE.connect('Excel.Application')
wb = xl.ActiveWorkbook

ev = WIN32OLE_EVENT.new(wb, 'WorkbookEvents')

ev.on_event('SheetSelectionChange') do
range = xl.Selection
puts(range.Value)
STDOUT.flush()
end

ev.on_event('BeforeClose') do
puts('Closed');STDOUT.flush
exit_event_loop
end

$LOOP = true

def exit_event_loop
$LOOP = false
end

while $LOOP
WIN32OLE_EVENT.message_loop
sleep(0.1)
end

And there you have it. Tweak to suit your individual needs.

Thanks for stopping by!

13 comments:

Dan said...

Thanks for the tip!

It's rough getting Ruby + Windows working smoothly -- appreciate you sharing your knowledge.

Karthi said...

Instead of 'BeforeClose' I prefer 'AfterClose' as there are chances that the user clicks on 'Cancel' in the save confirmation dialog and the workbook will remain open even after 'BeforeClose' was triggered.

David Mullet said...

Thanks for the kind words, Dan!

@Karthi-

There doesn't appear to be an AfterClose workbook event.

Are you trapping this at the Application level?

Can you post (or send me) a code snippet? Thanks!

David

Poida said...

You might be interested in my project: xlloop.sf.netThe framework lets you write MS Excel functions in ruby. See ruby server.

Tom said...

Hi Dave!
Great post (as usual ;-)
Do you know, whether it is possible to fire custom (OLE ?) events from excel (via RaiseEvent, see www.cpearson.com/excel/Events.aspx) and then connect to them?
I could not get that to work yet.

Anonymous said...

hi,
thank you for that super blog

I tried to adapt this example to word unfortunately without success. This is what I did

require 'win32ole'
word = WIN32OLE.connect('Word.Application')
active_doc = word.ActiveDocument
ev = WIN32OLE_EVENT.new(word, 'ApplicationEvents')
ev.on_event('WindowSelectionChange') do
puts(word.Selection.Text)
STDOUT.flush()
end
ev.on_event('DocumentBeforeClose') do
puts('Closed');
STDOUT.flush
exit_event_loop
end
$LOOP = true
def exit_event_loop
$LOOP = false
end
while $LOOP
WIN32OLE_EVENT.message_loop
sleep(0.1)
end

Any help appreciated. Thanks Marcel

David Mullet said...

@Marcel:

Change the name of the event sink from...

'ApplicationEvents'

...to...

'ApplicationEvents2'

...and your code should run as written.

David

Anonymous said...

Thanks a lot. That works just fine
Marcel

Jeremy said...

Thanks for the great information on your site. I use it often.

I would really like to be able to use an Excel command Button to fire an event in Ruby, but I don't know enough about excel to see if this is even possible.

If it's possible, i'd love to know how.

Thanks,
Jeremy

Josue said...

Thanks a lot for the tips, great post !

As Jeremy said, is it possible to handle command button events from Excel in Ruby ?

I really would like to know how to do !

Thanks

Josue

mindquake said...

Hi,

How do you find out which event sinks are available for different OLE objects? I have tried looking in the MSDN documentation and generally in Google but I cannot find them.
Any help would be much appreciated. Thanks for the great articles!

John

Kenny said...

Hmm, how about WorkSheetEvents?
@@xl_sheet_events = WIN32OLE_EVENT.new(@@xl_sheet, 'WorksheetEvents') won't work. searching all day to get just this to work...

Mike Karliner said...

Any idea how it might be possible to get the win32ole event loop to work with the event loop of, say wxruby?

cheers

Mike