Why is MS Excel VBO - Write Collection so slow?

[ScottRobson]

Any reasons why the Write Collection function in MS Excel VBO is painfully slow? A process we are developing is taking over 10 minutes to write a collection with < 10,000 rows (approx 15 columns, two of which contain Excel forumlas), but if we use Copy and Paste between Excel worksheets, it's almost instant. We have tried disabling auto-calculaton whilst the write is happening, but it's made no noticeable difference. Unfortunately, Copy and Paste isn't an option here, as we need to perform a series of logical steps with the data within a collection. Obviously not very good when a user can perform a job in seconds, which takes the robot many times longer due to this slow action. Is there a quicker method? Or could there be an underlying issue (eg: machine or BP config) which is causing the slow performance?

[ScottRobson]

Thanks Bastiaan, but makes me wonder why BP wouldn't just do that in the background if it was that straight forward. I'm keen to understand why the MS Excel VBO takes so long. Presumably it must do a lot of additional processing over and above what a simple copy and paste would do? Also, using the clipboard comes with its own risks, eg: if a Controller is viewing the process in a remote connection, they could inadvertently copy/cut something into the clipboard from their own machine, which could corrupt the data in the process. Unlikely, I know, but a risk none the less.

[andrea_frauchig]

I am looking for the same answer, ie the write collection seems to be awfully slow and I was looking into a better way to solve this. How exactly would that code stage look like on #2? Did someone try it out? thanks!

[DavidWhite]

To address #2: Wouldn't converting a 10,000 row (15 column) collection to a string (separated by a tab and each row by a linebreak) also be painfully slow?

[IanFarrington]

Blue Prism team: it  has been 5 years since this post, and not a single response from you. I have a hard time this particular post wasn't seen by any staff that monitor the community. Is this addressed in a certain version? This slowness is a black eye for automation, as I already have a Finance team whose perception of RPA is very low due to the fact that they'd rather do their manual process than wait over an hour for 30k lines to write from a collection to their Excel report. Please comment. Thank you.

------------------------------
Ian Farrington
------------------------------

[ewilson]

Hello @Ian Farrington,

What version of Blue Prism are you running? Have you looked at the MS Excel VBO recently? The most recent release can be found at the following link on the DX:

https://digitalexchange.blueprism.com/dx/entry/3439/solution/ms-excel-vbo

I'd call your attention to the action Write Collection (Fast). This action was added in an attempt to speed-up the process of the writing large collections to a worksheet. Have you tried it?

Cheers,
​

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

[IanFarrington]

Great timing for me to ask, I guess -- thanks, Eric! I will have my CoE check this out. It may be exactly what we need.

------------------------------
Ian Farrington
------------------------------