Friday, May 6, 2011

How to react to Excel events in Ruby?

I can find 10^1000 examples of scripting Excel using Ruby, but I can't for the life of me figure out how to have Ruby react to events in Excel. I'm trying retrieve the contents of a row in a worksheet when it's selected, but such an event-based retrieval I can't find any methods or examples for.

From stackoverflow
  • Use the *WIN32OLE_EVENT.new* method to create an OLE Event object, then call its *on_event* method. Call the *WIN32OLE_EVENT.message_loop* method to launch the event-monitoring loop.

    Here's an example that prints out the selected range's value, then halts event monitoring before the workbook is saved:

    require 'win32ole'
    
    def exit_event_loop
        $LOOP = false
    end
    
    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('BeforeSave') do
        exit_event_loop
    end
    
    $LOOP = true
    while $LOOP
        WIN32OLE_EVENT.message_loop
        sleep 0.1
    end
    

    Hope that helps.

0 comments:

Post a Comment