topic RE: open ODC extension file with power query using blue prism in Product Forum

open ODC extension file with power query using blue prism

Miya — Mon, 08 Aug 2022 15:27:00 GMT

Hi,

I have the power query set up in a ODC file, i tried using ms excel VBO to open the file by in blue prism i get error saying the collection not available.

Please can some one suggest what is the best way to open ODC extension file using blue prism

------------------------------
Miya
------------------------------

RE: open ODC extension file with power query using blue prism

ewilson — Mon, 08 Aug 2022 15:57:00 GMT

Hello @Miya,

There is no support for Power Query in the existing Excel VBO. You might take a look at the PowerBI integration available on the DX. It might be of use to you.

https://digitalexchange.blueprism.com/dx/entry/3439/solution/power-bi-integration-2

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: open ODC extension file with power query using blue prism

john.hammond — Tue, 09 Aug 2022 12:29:00 GMT

Hi Miya. I run a similar process that uses ODC files. I take it that you have created the Query in Power Query and exported the connection to create the ODC file?

Within Blue Prism, using the latest Excel VBO, I point the 'Open Workbook' action on the ODC file and it then populates. Depending on the size of data or the complexity of the query, you might need to extend the Timeout variable within the Open Workbook action. Let me know if you need any further assistance.

------------------------------
John Hammond
------------------------------

RE: open ODC extension file with power query using blue prism

Miya — Tue, 09 Aug 2022 14:20:00 GMT

Hi John,

Thank you for the reply, so if i open the file using ms excel VBO open work book will populate the values?, pls can you tell me how to extend the time out variable . Currently my file takes around 2 mins to open excel manually.

Thanks & Regards,
Arthi

------------------------------
Miya
------------------------------

RE: open ODC extension file with power query using blue prism

john.hammond — Tue, 09 Aug 2022 14:25:00 GMT

Yeah, that's what I do - I point the Open Workbook action to the ODC file, and the Open Workbook action doesn't complete/move on to the next action until the data is populated in an active Excel session.

As far as extending the Time Out variable, it's just a case of viewing the Open Workbook action within the Excel Object and changing the Timeout value (in seconds). I can't remember what the default was, to be honest, but I have it for 600 seconds, as my process transfers data from multiple SharePoint sites which slows it down somewhat. You might be fine with the default setting if it takes about 2 minutes.

------------------------------
John Hammond
------------------------------

RE: open ODC extension file with power query using blue prism

JackKidman — Tue, 09 Aug 2022 16:40:00 GMT

As far as extending the Time Out variable, it's just a case of viewing the Open Workbook action within the Excel Object and changing the Timeout value (in seconds).
I can't remember what the default was, to be honest, but I have it for 600 seconds, as my process transfers data from multiple SharePoint sites which slows it down somewhat. You might be fine with the default setting if it takes about 2 minutes.

Hidden Trails, Ltd.

RE: open ODC extension file with power query using blue prism

Miya — Tue, 09 Aug 2022 20:22:00 GMT

Thank You John.

I was able to get the details in excel and trying to save it and get it into collection. Is that possible with the odc extension itself.

------------------------------
Miya
------------------------------

RE: open ODC extension file with power query using blue prism

john.hammond — Wed, 10 Aug 2022 07:54:00 GMT

That's good to hear. I'm not sure if I entirely get what you mean by trying to save/get into a collection with the ODC itself. For the process that I deal with ODC files, I open the ODC using the Open Workbook action like as described above. After that, I have this set of actions (all from within the Excel VBO): Get Worksheet Name, Get Number of Rows, (here I do a check to make sure that the ODC has actually returned data. I also do a calc stage to set the last cell of the data that the query has returned - in my case, "V" & ([Number of Rows on Query] + 1)), and then finally Get As Collection (with the EndCell parameter taking on the value of the last cell calculation I mentioned above).

Once you've got it as a collection, you can do whatever you like with the ODC/Excel session. I do save the query results as I need to refer to them later on in the process, and it's a lot more efficient to re-open the saved query response than it is to run the query again in its entirety.

------------------------------
John Hammond
------------------------------