Work properly with Excel

[NiklasFranke]

Hello together, 

i just asked myself, how do i work properly in Excel. I mean, i can get the worksheet as an collection, which is i think faster, but the Collection VBO is missing some things, wich the Excel VBO has.

If i get a worksheet as collection and want to search for a certain Value in a Row to get Column index, its just not possible, without looping all columns and increasing the Columnindex.

+ If i insert the Collection back at the end, all formulas etc, that where in the Range are gone (if there where some in the data).

When i use "Collection contains Value" i dont even get a rowindex and i had to extend the code from the VBO which i find should be a basic function..

For Example, ich search in Column for a Value and want to change other Values in this Row, its not possible, without the Rowindex.

And if i Filter Collection, i just get the row, so i would have to change the Values and insert it back at the Worksheet which i cant, because of the missing Row Index

The Excel VBO is way more easy. Just use Find in Worksheet, also you can use a Row to search,

and you have all the needet index to work with and change the Values directly in the worksheet.

Am i right or are there better ways to work with Excel?
Thanks for your help 🙂

[Michael_S]

Hi @NiklasFranke! I'm with you on this one - right tool for the job. You might want to check out our community's top VBO recommendations here, there's some good ones you might not have seen.

On working with Excel, quite a few members have commented that the Microsoft Graph API VBOs are even better - since they don't require any of the Office products to be installed on the digital worker.

[NiklasFranke]

Hello @Michael Shillingford and thanks for the answer.

Would you say, that the Collection VBO works faster than the Excel VBO?

F.e. if i want to set like 10k Values+?

I cant really say, if this would be like 50% faster etc. than the Excel VBO.

If i remeber it right, i think, that the Collection VBO is way faster with huge changes but im not sure.

I allready have automated a process with Excel files with 15k rows and even with the Collection VBO the process needs about 4 hours.