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)

The Excel document can have code inserted within it via Powershell
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?

So you have no access to edit the macro? This would be simpler if you could just add an argument to allow it to run silently and not pop-up the message box. — TheMadTechnician, Jul 07 '14 at 22:40
Won't you know it is ok from satisfactory completion of your powershell script? — brettdj, Jul 08 '14 at 06:45
Well.. I wouldn't because the messagebox freezes the macro that is run, and the powershell is waiting for the box to go away. — monksy, Jul 08 '14 at 06:53
Ok. But why can't you remove the message box (or userform) from the VBA macro? — brettdj, Jul 08 '14 at 07:37
The incoming file cannot be modified. I have no control over that. — monksy, Jul 08 '14 at 07:57

score 1 · Answer 1 · edited May 23 '17 at 12:29

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!

edited May 23 '17 at 12:29

Community

1
1

answered Jul 07 '14 at 22:26

hnk

2,216
1
13
18

The OP is referring to `Powershell`, not `VBA` (1) or a `vbs` (2) – brettdj Jul 08 '14 at 06:46
1

Thanks @brettdj, Yes, but if the problem is requirement specific and not Powershell specific, this solution can be used. Moreover, the way to asynchronously communicate with the macro for VBA could be used for the specific use-case. Also @ monksy, if it's not relevant to your problem, I'll be happy to take it off. Tks – hnk Jul 08 '14 at 10:09

Running a macro that opens a MessageBox

1 Answers1