Run macro (Excel VBO)

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

  • 1.JPG
    77.9 KB · Views: 454

VJR

Well-Known Member
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 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

  • Action1.JPG
    50.4 KB · Views: 475
  • Action2.JPG
    48.1 KB · Views: 420
  • Macro.JPG
    49.4 KB · Views: 391
  • Process.JPG
    67.2 KB · Views: 393

VJR

Well-Known Member
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)
 
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
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 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
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
 

breagladney

New Member
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 2448


Can you provide steps for running a .vbs file in Blue Prism?
 

DBH

New Member
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

Hi Aleksandr,
When you said "it works now", would you mind to explain what you changed to make it work? I am having the same problem (the error message). I use Create Instance> Open Workbook, Show> then Run Macro. The handle I use on the Run Macro is the handle generated as output on the Open Workbook stage.
 

Sasi

New Member
When you do excel operation using VBO, if you mix Excel VBO and Excel VBO-Extended, then you will the issue as "Given key not present". If you start with Excel VBO, make sure you use same for you entire operation until you close the excel instance.
 

DBH

New Member
Hi Sasi, you are a life saver, thanks. You were right I had a mix of Excel VBO and Excel VBO-Extended. Now I only used Excel VBO and it worked.
 
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
hi,
can u pls provide me the steps of creating a macro. am a beginner in bp and rpa. i tried to implement macro and am getting this same error but am not understanding with the action stages. pls provide a step by step guide to execute a macro from bp

thank you
 

SriLakshmi

New Member
Q)Can anyone help me in passing parameters to macro from blueprism?

When i tried calling the macro function in VBA ( and passing parameters), its absolutely working fine.

When tried passing parameters from blueprism, am getting below error:
Internal : Could not execute code stage because exception thrown by code stage: Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))

Is there any particular syntax to be followed to pass parameters from blueprism.
PFA my code snippets.

FYI...When i tried running macro from blueprism (hardcoded parameter values) without parameters, its absolutely working fine (as expected).
So,
Macro is working with and without parameters (from VBA).
Macro is working without parameters (From Blueprism)
Macro not working with parameters from blueprism.

Request your help
 

Attachments

  • Code Stage-Inputs.JPG
    30.5 KB · Views: 116
  • CodeStage.JPG
    25.1 KB · Views: 104
  • Calling MacroFun-from VBA-working fine.JPG
    12.6 KB · Views: 103

Shweta

Active Member
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 2448
How exactly you did that? I too have similar requirement, can you please share the detailed steps on how can I run the VB script. What did you write in that
 
Top