topic RE: Working with Excel in Product Forum

Working with Excel

GAVIN.RUDLING — Fri, 25 Nov 2022 09:38:00 GMT

Hi Team, Trust you are all well and looking forward to the festive season ahead.

I have a slight issue when working with an excel macro file and I am sure it is not a Blue Prism issue at all.
I am hoping someone may have a hint on the problem.

A client has provided an excel macro file, basically just to refresh some data tables and pivot tables linked to their SQL database.
When the client connects to the folder via a Forticlient VPN connection and executes the file, it runs in under 5 - 10 seconds.
However, If I connect via the same VPN and execute the same macro file, it takes between 10 and 15 minutes if lucky and I get quite a few connection timeout errors in the VBA code itself. There are occasions where the run is successful but still very slow.
I have tried the this from The BPC platform and from my local machine with the exact same results.
I am not sure if it is to do with the location where the excel macro file is being hosted.
Anybody have any ideas?

Kind regards
Gavin

------------------------------
Gavin Rudling
Digital Consultant & Developer
Cog3nt
Africa/Johannesburg
0813906789
------------------------------

RE: Working with Excel

PvD_SE — Fri, 25 Nov 2022 10:05:00 GMT

Hi Gavin,

In short: XLs with active macros are a pain in the behind! I try to avoid them best I can, but sometimes you got to live with them - I do so reluctantly.

While I do not have the problem you describe, I would recommend to have a look in community 'Blue Prism Product' instead of the one you posted in. I recall there's quite a few macro related questions there that possibly provide the answer to your question.

------------------------------
Happy coding!
---------------
Paul
Sweden
------------------------------

RE: Working with Excel

GAVIN.RUDLING — Fri, 25 Nov 2022 11:34:00 GMT

Hi Paul, Thanks for the advice and direction, will do that.

Kind regards

Gavin

RE: Working with Excel

michaeloneil — Fri, 25 Nov 2022 11:57:00 GMT

Hi @Gavin Rudling

I would say avoid using the macros within the file, the best thing would be to simply replicate the actions the macros are doing in your process. If its just a refresh of pivot tables then thats a simple enough action for the bot to handle using a code stage. With macros your basically automating something thats already automated and i normally try to avoid that scenario.

I did have a client ask for this before and they essentially wanted to automat excel front end just so it could click the macro buttons and then the pop up windows. I did a poc to demo the bot doing all the same actions as the macros which was faster but they insisted so in the end we compromised and let the bot use the file but initiate the macros using the MS Excel vbo > Run macro action. But it did require me to walk through their vba with them to advise where to disable message boxes so it didnt cause a failure in the bot.

So simply put blue prism and macros are not the best of friends :P

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

RE: Working with Excel

GAVIN.RUDLING — Fri, 25 Nov 2022 12:22:00 GMT

Thanks Michael, I agree.

The main issue is the process is working fine and it executes the macro. However it just seems to take for ever on running it from the BPC environment and or my local machine where if the client executes the same macro via the same VPN connection it runs in under 10 seconds.

So I am looking for an understanding as to why the difference.

I get the same result triggering the macro without Blue Prism. So I suspect infrastructure or something like that that can cause the time difference.

You mentioned a code stage to refresh pivots or Data tables, do you perhaps have an example of the code stage that I could add to the Excel VBO?

I am fine with the VBA code but the implementing the code in the code stages, I am out of practice and need some time spend there.

Kind regards

Gavin

RE: Working with Excel

michaeloneil — Fri, 25 Nov 2022 15:27:00 GMT

Hi Gavin

No problem I think the below code should do what you need. If you need to change the data source of the pivot then there is an action already for this in the latest version of the excel vbo from blue prism. Hope this helps.

Dim ws as Object = GetWorksheet(handle,workbookname,worksheetname)

ws.Activate()

ws.PivotTables(Pivot_Name).PivotCache.Refresh

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

RE: Working with Excel

GAVIN.RUDLING — Sun, 27 Nov 2022 18:16:00 GMT

That's great, thanks Michael, mush appreciated

Kind regards

Gavin