Excel VBO Paste Collection into Workbook Speed

[MichaelNiemeyer]

I had an interesting observation earlier while watching a process running - the default action to paste a collection into Excel was taking roughly six seconds per line. However, if I were to hold down the left click of the mouse, the speed increased to 1.5 lines per second. Does anyone have an idea what causes this difference in speed or have an idea how to implement this knowledge into increasing the speed of this action? My process is pasting thousands of lines per month, so this could result in dozens of hours saved, especially when up against a system deadline.

[AndreyKudinov]

No idea why clicking mouse makes default action faster - try disabling events. Maybe holding LMB somehow does the same thing. In general, if you want to write to excel faster, rewrite default action to move collection to object array, then write it to excel in one OLE call (instead of writing cells one by one). You'll have a huge performance gains on collections over 1000+ rows (it writes all in a few seconds this way). 

[BenAnderson]

Hi @AndreyKudinov,

I came across this post while experiencing a similar Excel issue. Would you be able to elaborate on how to "rewrite the default action to move the collection to an object array" please, as per your previous post? ​How do I go about writing it in one OLE call?

Many thanks for your help,
Kind regards,
Ben

------------------------------
Benjamin Anderson,
Senior Consultant,
UK
------------------------------

[PvD_SE]

Hi Ben,

The 'rewrite' is not so much a programming effort as it is deploying the BP OLEDB object rather than another BP VBO to extract data from an XL. The OLEDB object can be used to SQL against an XL file and get its contents to a Collection. As it does use a better/more speedy method, it uses less memory which can be a life saver for large XLs. Search the community for OLEDB and you should find many postings on this subject.

Example:
https://community.blueprism.com/communities/community-home/digestviewer/view-question?ContributedContentKey=f5603485-f4b5-4db8-99d6-f87551a0978a&CommunityKey=3743dbaa-6766-4a4d-b7ed-9a98b6b1dd01#15210...

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

[MichealCharron]

@Benjamin Anderson,

If you download Blue Prism's latest MS Excel VBO from the DX (Blue Prism Digital Exchange - Blue Prism's MS Excel VBO), there is a action called "Write Collection (Fast)" which does what @AndreyKudinov was talking about (all those years ago).​​

------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------

[BenAnderson]

Thanks @PvD_SE, I'll certainly check that out thanks.
Ben​

------------------------------
Benjamin Anderson,
Senior Consultant,
UK
------------------------------

[BenAnderson]

Hi @MichealCharron, I'll have be sure to have a look at that.
Many thanks for your reply,
Ben​

------------------------------
Benjamin Anderson,
Senior Consultant,
UK
------------------------------