Run macro (Excel VBO)

#1
Guys, hello

Need your help, as I have faced an issue with "Run macro" action.

"ERROR: Internal : Could not execute code stage because exception thrown by code stage: The given key was not present in the dictionary."

That's the error message I get when running "Run macro" action. Attaching the screen of process flow and the inputs for "Run Macro", which seem ok for me.

Thank you
Aleksandr
 

Attachments

  • 77.9 KB Views: 30

VJR

Well-Known Member
Staff member
#2
Hi Aleksandr Nikulin,

That error usually occurs when there is an issue either with the parameters or the Excel instances, or the Handle or the Workbook name, Path, etc either is missing something or is not configured correctly. So take a look at all those thoroughly first.

To identify whether there is an issue specifically with the Run Macro action, temporarily remove the Run macro action and do something else with the Excel sheet like copy paste data from one sheet to another etc. If it works fine then perhaps the Run Macro has an issue. Then check everything related to the macro, its spelling, does it take parameters. etc. Create a simple sub test() in a new module with a MsgBox "testing" and check whether it shows the message box using Run Macro or does it give the same error. Also check that the macros as enabled at the time when the file opens up from the Show Wb action.
 
#3
Hi Aleksandr Nikulin,

That error usually occurs when there is an issue either with the parameters or the Excel instances, or the Handle or the Workbook name, Path, etc either is missing something or is not configured correctly. So take a look at all those thoroughly first.

To identify whether there is an issue specifically with the Run Macro action, temporarily remove the Run macro action and do something else with the Excel sheet like copy paste data from one sheet to another etc. If it works fine then perhaps the Run Macro has an issue. Then check everything related to the macro, its spelling, does it take parameters. etc. Create a simple sub test() in a new module with a MsgBox "testing" and check whether it shows the message box using Run Macro or does it give the same error. Also check that the macros as enabled at the time when the file opens up from the Show Wb action.
Hi, VJR

Thanks for reply.

1) I tried to play around with different excel action (instead of "run macro") and it works well - created additional excel worksheet, which uses the same inputs [handle] and [WB name], so seems it is ok with them.
2) I created one other simple macro and faced the same exception.
3) Code of Run Macro action itself seems ok for me, I also checked that he takes the parameters.

So, issue is still there, sending you some new screenshots:

1) Macro.jpg - just to show the excel file name and macro name.
2) Process.jpg - process flow with Run Macro parameters.
3) Action1.jpg - Action itself with inputs
4) Action2.jpg - Action with code stage

So, the onlything I assume that the Macro name is incorrect, should there be some kind of extension? As I said previously, [handle] and [WB name] seem ok, as they worked with other action I tried and moreover, they are filled by previous actions withing process flow.

Thank you
Aleksandr
 

Attachments

VJR

Well-Known Member
Staff member
#4
Hi Aleksandr Nikulin,

I have checked all of your screenshots and they appear to be fine except something fishy with the process.jpg.
The combination of Close all instances -> Open instance -> Open Wb doesn't appear seem to go well with the handle.
When I mentioned "parameters not configured correctly" then it is something like this.
You may have tried the same diagram with the new simple macro that you created and hence that too threw the same error.

Try it as below:
- A fresh new page or a process diagram where none of the earlier handles/instances should be used.
- Create Instance (Not Open Instance)
- Open Workbook
- Show
- Run both the macros and check (the simple one and the Initiate Search)
 
#5
Omg, I dont know what I did differently, but it works now! (it was create instance stage with a name open instance) Anyway, big thanks for your time and help!

I would appreciate if you can help with another thing just appeared - when macro is executed and search performed, there are 2 pop-up windows, which require some additional clicks (first regarding Save As window, where you just need to click SAVE, and the next when document is saved and there is an informative message with OK button, that everything is done). The problem is that BP won't pass that "Run macro" action until you click those pop ups, meaning that I am not able to spy those and iteract with them until "Run macro" is done. Does it mean I have to rebuild the macro and make it without any pop ups, if I want to use "Run macro" stage?

Thanks in advace
Aleksandr
 

VJR

Well-Known Member
Staff member
#7
Hi Aleksandr Nikulin,

If a macro is running through BP then it is always better to make it unattended. Meaning - without having to attend it to do the manual clicks. So if its possible to remove the SaveAs window and the informative Ok button then do that. I do not think it may require to "rebuild" the macro to do just this.

On the other hand if you cannot do that then you can still spy the SaveAs window and the message box, Attach and interact with them through BP. You can look for some popup and SaveAs window related posts on the forum and will know on interacting with them.
Post back what you came up with.
 
#8
Hi Aleksandr Nikulin,

If a macro is running through BP then it is always better to make it unattended. Meaning - without having to attend it to do the manual clicks. So if its possible to remove the SaveAs window and the informative Ok button then do that. I do not think it may require to "rebuild" the macro to do just this.

On the other hand if you cannot do that then you can still spy the SaveAs window and the message box, Attach and interact with them through BP. You can look for some popup and SaveAs window related posts on the forum and will know on interacting with them.
Post back what you came up with.
Hi VJ

Yes, sure I can spy and interact with them through BP. But the problem is that the "Run macro" stage is running until manual click is performed, so BP will not go to the next stage until Save As window is open, meaning I am not able to interact with all the sub-windows through BP within the process.

So, the only thing is still making it unattended, I guess.

Thanks
Aleksandr
 

VJR

Well-Known Member
Staff member
#9
Hi VJ

Yes, sure I can spy and interact with them through BP. But the problem is that the "Run macro" stage is running until manual click is performed, so BP will not go to the next stage until Save As window is open, meaning I am not able to interact with all the sub-windows through BP within the process.

So, the only thing is still making it unattended, I guess.

Thanks
Aleksandr
Alright, I see what you are saying now. I can let you know one suggestion and you could see if that works for you.

- When I ran an Excel macro (with a message box) from a script file like .vbs and called the vbs file from Blue Prism, the diagram executed till the final stage, even when the message box was still showing up. You could now interact with the message box or the SaveAs window via Blue Prism.

- When the same macro was run directly from BP using the Run Macro action, the message box was shown but the diagram was stuck on the Run Macro action until the OK button was pressed just like you said.

So that could be an alternative approach you could use or the option would be to go by modifying the macro.

View attachment 1541104172865.png
 
Top