Run Power Query from Blue Prism

[NdrtimDauti]

Hi,
I have a query in Excel that is connected to multiple files in order to run return two tables. I would like to run power query from blue prism.
I have a 6.10.1 version and Excel 2013.
There is no excel VBO action, so what would you recommend? 
I have seen following ideas/suggestions:
1. Create Business object with code stage that will basically generate a refresh all in excel
2. Save the queries as ODC and open them in BP

Following errors:
1. Tried with a code stage that will take handle and Workbook name from Items from the process.
2. It is hard because multiple queries which need to be saved separetelly and don't know how to make them interact in BP.

Here the code with the error in line  6 (Get workbook not declared) I am new with this coding type, it is a modified version of a suggestion that I found in another topic.

Thank you all : )

------------------------------
Ndrtim Dauti
------------------------------

[kkazantsev]

@NdrtimDauti , here're the steps to try:
1. In Excel create a macro "abc" to execute the query and populate two tables.
2. In the beginning of the macro set some status cell  value to "running"
3. When the macro is done, set the status cell value to "done"
4. In Blue Prism, use Excel Object "Run Macro" Action to execute the macro
5. Have a loop with desired expiration time that periodically checks the value for the status cell 

------------------------------
Konstantin Kazantsev
Solutions Architect
Church and Dwight
America/New_York
------------------------------

[Asilarow]

Why not read the files with BLue Prism instead, and return the contents into collections?

------------------------------
Andrzej Silarow
Principal Consultant
Ignite IPA
Europe/London
------------------------------

[kkazantsev]

@Andrzej Silarow , reading file directly is absolutely fine if the query data already filled in. If Refresh needs to happen on demand, Blue Prism needs to trigger it and then wait for results to be filled in.

------------------------------
Konstantin Kazantsev
Solutions Architect
Church and Dwight
America/New_York
------------------------------

[DACADBSA]

I have a few processes that run PQ and these are the steps I use.  

the only difference is that if you untick the Enable background refresh option in the query properties, you don't need step 5.  The Run Macro stage will wait until the macro has finished.  The macro will only finish when the query has finished. 

I still populate a cell with a success message and check this cell after the macro has ran to make sure the data has been populated correctly.  Basic error handling in the macro helps make blue prism recover if the PQ has an issue while running

------------------------------
Daniel Caddick
------------------------------

[NdrtimDauti]

Hi Daniel,

Which version of BP are you using?
I have 6.10.1 and the Excel Object "Run Macro" Action is not working. (That is why we are not using macros in BP , although it would make the job much easier)

 I unticked the Enable background refresh option in the query properties.

Thanks 

------------------------------
Ndrtim Dauti
------------------------------

[DACADBSA]

Hi,

We are also running that version of BP.  Here is the way we have it set up for one of the processes.

Hopefully those images are self explanatory.  

If it still doesn't work, maybe check the security and trust centre settings on excel to make sure marcos are enabled to run.  Obviously save as a macro enabled workbook rather than .xlsx.  Not sure what else to suggest really.  Best of luck

------------------------------
Daniel Caddick
------------------------------