Answered

Process cannot wait for VBA to proceed before continuing

(0) Share

Report

Posted on by alee001

422

The PAD process (below) cannot wait for the VBA to complete an error. (As shown in the picture), is there any other way to solve it?

For each file in list	'(total 20)
 get window
 edit cell	
 run vba 	'(run 20~30s)
 wait 2s	
next 	'(not wait vba run finish)
For each file in list	'(total 20)
 get window '(error due to vba running to the 9th or 10th file)
 goto sheet1	
 save	
 wait 2s	
next

螢幕擷取畫面 2023-07-03 235107.png

Categories:

Power Automate Desktop

All responses (11)

Answers (1)

Sort by

Agnius Bartninkas Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

I only see Send keys actions there. If you need to run a macro, there is an action in the Excel actions group to do that. It's called Run Excel macro in English. Use that and the flow will wait for the code to complete. When you run it via Send keys, it will obviously not wait, as all it knows is that it's sending keystrokes.
alee001 422 on at

Like
a
(0)

Report
Copy link

Link copied!

Because all files are not opened from PAD, the cell change is interactive with VBA execution, so [Run Excel macro] is not applicable.

I want PAD to wait for the VBA to complete before continuing instead of terminating with an error.
MichaelAnnis 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Do you own the Macro creation? If so, set a message box to appear as the very last step in the macro.

you can have the bot wait for the message box element and then have the bot click ok. This will offer a trigger for moving on.
alee001 422 on at

Like
a
(0)

Report
Copy link

Link copied!
You can also use the method you suggested for a single marco, but my marco is the entire worksheet as follow:
Private Sub Worksheet_Change(ByVal Target As Range)
Agnius Bartninkas Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

You can use the Attach to running Excel action to attach to a running instance of Excel and generate an Excel instance. This will allow using the Run Excel macro action, which will also make the flow wait for the macro to complete.

If this is still not applicable to you, the only way to make the flow wait for it to end is, like @MichaelAnnis suggested - to have the VBA code trigger some sort of a UI event when it's done, and then making the PAD flow wait for such an event. A message box is the easiest to implement.
alee001 422 on at

Like
a
(0)

Report
Copy link

Link copied!

Now all files in the loop flow are almost the same as executing VBA. If you want to complete each VBA and add a dialog box, the whole process will slow down.

I can only solve it by increasing the wait time on flow of each loop file from 4 seconds to 10 seconds.
Agnius Bartninkas Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

I honestly don't see how increasing a fixed wait will not slow it down, while having a message box will. Both approaches will slow the flow down, and I would argue that adding a fixed wait will probably slow it down more.

Again, I would suggest looking into calling the macro via PAD.
alee001 422 on at

Like
a
(0)

Report
Copy link

Link copied!

Extended wait time just buffers the flow.
Agnius Bartninkas Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

That is assuming you are 100% certain your VBA will always take 10 seconds to execute. If it takes less than that, your buffer is making the flow run longer than it could.
MichaelAnnis 5,727 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

And if it takes more, then your flow won’t work 😞