Running excel macro and Sql statements in Blueprism

[mgagarin1]

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

[krishna.kodimurali9]

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.

[ritansh.jatwani]

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.

[mgagarin1]

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

[diane.sanzone]

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.

[EVIPUTI]

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.