How can I call a long-running external program from Excel / VBA?

Question

What is the best way to run an external program from excel. It might run for several minutes. What's the best-practice about how to to this. Ideally,

1. A model dialog box that let's the user know that the process is executing.
2. If the executable fails, the user should receive a notification.
3. A timeout should be enforced.
4. A cancel button should be on the dialog box.

But any best-practices are welcome. I'm interested in solutions with calling either a .dll or an .exe. Preferably something that works with Excel '03 or earlier, but I'd love to hear a reason to move to a later version as well.

Answer 1

You should check out these two Microsoft KB articles

How to launch a Win32 Application from Visual Basic and

How To Use a 32-Bit Application to Determine When a Shelled Process Ends

They both quickly give you the framework to launch a process and then check on its completion. Each of the KB articles have some additional references that may be relevant.

The latter knowledgebase article assumes that you want to wait for an infinite amount of time for your shell process to end.

You can modify the ret& = WaitForSingleObject(proc.hProcess, INFINITE) function call to return after some finite amount of time in milliseconds--replace INFINITE with a positive value representing milliseconds and wrap the whole thing in a Do While loop. The return value tells you if the process completed or the timer ran out. The return value will be zero if the process ended.

If the return value is non-zero then the process is still running, but control is given back to your application. During this time while you have positive control of your application, you can determine whether to update some sort of UI status, check on cancellation, etc. Or you can loop around again and wait some more.

There are even additional options if the program you are shelling to is something that you wrote. You could hook into one of its windows and have the program post messages that you can attach to and use as status feedback. This is probably best left for a separate item if you need to consider it.

You can use the process structure to get a return value from the called process. Your process does need to return a value for this to be useful.

My general approach to this kind of need is to:

1. give the user a non-modal status dialog at the start of the process with a cancel button, which when clicked will set a flag to be checked later. Providing the user with any status is most likely impossible, so giving them an elapsed time or one of those animated GIFs might be helpful in managing expectations.
2. Start the process
3. Wait for the process to complete, allowing cancellation check every 500ms
4. If the process is complete close the dialog and continue along.
5. If the process is not complete, see if the user hit cancel and if so send a close message to the process' window. This may not work and terminating the process might be required--careful if you do this. Your best bet may be to abandon the process if it won't close properly. You can also check for a timeout at this point and try to take the same path as if the user hit cancel.

Hope this helps, Bill.