Automatically Confirming a Msgbox in Excel VBA code

Question

I am using ruby a WIN32OLE to automate execution of Excel macros with windows scheduler.

I have all of this working properly. My problem comes with having to run a macro embedded in an excel file of an external party (ie I don't have control of the macro). I can call the macro with no problem, however the code contains user prompts (in the form of Msgbox statements)

In short how do I tell the msgbox "yes" or "ok" via WIN32OLE?

What I have tried

• Event handling: The Msgbox does not throw a worksheet event. I would have to think that windows throws an event somewhere for the Msgbox (but I am not skilled enough to capture it with WIN32OLE)

-Threading and Sendkeys: I was also unable to get the msgbox to respond to sendkeys. More problematic was that I was unable to get the threading to work with the WIN32OLE object (it worked fine when I was using puts and sleeps to simulate the macro running.)

It appears I am stuck with copying the code into another workbook and removing the msgbox statments, I don't really want to do that because then it will add maintenance work everytime the business logic of the 3rd party code changes.

Answer 1

Here's one way, install Autoit and then do:

au3 = WIN32OLE.new("AutoItX3.Control")
au3.ControlClick("MessageBox Title",'', 'OK')