Running a macro that opens a MessageBox

Question

I'm invoking a macro within an Excel document via Powershell.

To invoke the macro, I have to run a named macro and call it in run. However, when the macro is invoked and completes successfully, a MessageBox will appear. As far as I know, this messagebox is the only way to find out if the process has completed successfully.

I have no control over the ability to remove the message box. The powershell script must wait for the macro to finish.

From the document: The documentation for this does not give an option for this situation, or so it appears.

Alternative Options I can work with: (But I'm not sure how to get to the point where the messagebox would be dismissed)

1. The Excel document can have code inserted within it via Powershell
2. Options can be changed within the document memory space

Is it possible to run a macro asynchronously and to check back on the execution of the macro?

Answer 1

There are a few convoluted ways to go about doing this.

1) Invoke another instance of the Excel application and run the Macro using that. Then how would you know whether it's done? You pass a global variable by reference to it. And use the OnTime functionality to keep checking every few seconds if its done or not.

An example for calling another excel instance is given here: Stop VBA-Script from "freezing" while sending MDX-Query

2) You can store your script as a .VBS file. Then you call the shell to run the VB script and again check some passed-by-reference variable.

3) Use a hidden worksheet as a buffer, which gets written upon in a particular location once the asynchronous code finishes running. Again, you need some clever OnTime programming to automatically run a polling service... and more importantly, to stop running it!