topic Re: Running excel macro and Sql statements in Blueprism in Product Forum

Running excel macro and Sql statements in Blueprism

mgagarin1 — Wed, 04 Aug 2021 16:15:38 GMT

Hello,
I would like to gather steps on how to run below in BP
1) Excel macro
2) an SQL

User doing manual update in one of our application and either via macro or sql can be executed thru BP.
I will think this is doable but I dont know how to. Can someone provide how to?

Appreciate it very much.
Marilyn Gagarin
United Rentals, Inc
CT/USA

Re: Running excel macro and Sql statements in Blueprism

krishna.kodimurali9 — Thu, 05 Aug 2021 06:58:53 GMT

You can run an excel macro using the below action, just you need to provide the Instance and the exact macro name that is present inside the excel workbook.

Re: Running excel macro and Sql statements in Blueprism

ritansh.jatwani — Thu, 05 Aug 2021 20:15:11 GMT

Hi @mgagarin1,

1) For running a macro, please use "Run Macro" action of MS Excel VBO and provide the macro name.

2) For executing a SQL query, please use "Data-SQL Server" VBO. First, you need to set a connection using Set Connection action and providing the below details:

Post that use "Execute" action of same object to execute any query.

Re: Running excel macro and Sql statements in Blueprism

mgagarin1 — Tue, 10 Aug 2021 16:26:19 GMT

Thank you Murali and Ritansh for information. The excel .xlsm file contains 4 macros, see below. Do I have to specify all of them under Macro name? And also, it has a commandbutton so now I am thinking if this Run Macro will handle this? My plan is to run the macro via BP instead of user running it manually.
I might be heading in a wrong direction so any input/advice is greatly appreciated.
Thanks again.
Marilyn

Re: Running excel macro and Sql statements in Blueprism

diane.sanzone — Wed, 11 Aug 2021 12:24:53 GMT

Hi Marilyn,

I have executed Excel macros exactly as you picture below where the business unit has added a button on the spreadsheet itself. The Macro "button" is really more for end users to simplify macro execution. The bot will reference the macro by name as Murali and Ritansh described. If you need to run the four macros sequentially, you would have 4 "run macro" actions in your process, calling each macro individually and in the proper order. Alternatively, if you want to use the button, you can create a custom object to interface with it. I don't know if the button element will be spy-able directly as a Win32 element- you might need to use Surface Automation.

Please be advised, however, that if the macro execution fails and the VB debugger pop up is displayed, the bot does not handle that well and will just wait until someone manually closes the pop up. You might consider also a custom object to interface with that window (I've had that happen, too). It's not difficult to build, but it is something to keep in mind.

I hope this helps.

Re: Running excel macro and Sql statements in Blueprism

EVIPUTI — Fri, 03 Sep 2021 05:20:23 GMT

As rightly said you should call the Run Macro action from the MS Excel vbo to achieve it through Bp . To deal with multiple files create a fresh excel file with extension .xlsm and write your macro code and run it from Bp. Also if your macro is constant you can create a new action in MS Excel VBO for the same.
For sql queries use Data - SQL VBO.