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!