topic RE: MS Excel VBO - Get Worksheet as collection no rows - BP Version v7.1.2 in Product Forum

MS Excel VBO - Get Worksheet as collection no rows - BP Version v7.1.2

ddtr_2022 — Fri, 05 Jan 2024 13:50:00 GMT

[Repost from earlier request]

Hi,

Is anyone having issues when using the action get worksheet as collection?

Our problem is that sometimes it is not getting any data into the collection which means 0 rows when it's not true. Is not throwing any kind of error and if I retry the action then it will work.

This is happening very randomly and the frequency of this issue is very variable as well, definitely not every day, and with totally different processes/excel files.

Any thoughts about this?

Thanks

------------------------------
Dat Duc Tran
------------------------------

RE: MS Excel VBO - Get Worksheet as collection no rows - BP Version v7.1.2

Denis__Dennehy — Fri, 05 Jan 2024 16:08:00 GMT

No idea - it should always work.
If it is a spreadsheet with lots and lots of data maybe it is a PC performance issue opening the spreadsheet fully - you could try putting a wait stage in between open and get data to see if that solves it. Probably also put in some exception handling if no results are returned to wait a bit and they try again.

None of that solves the why... but might help make it go away?

RE: MS Excel VBO - Get Worksheet as collection no rows - BP Version v7.1.2

petersjo — Mon, 08 Jan 2024 16:18:00 GMT

I've not had this issue, however you could put in a stage before it to copy the spreadsheet to the local machine (I'm assuming it's normally on a networked drive) and then reading it into a collection from there. This would at least eliminate the issue being network connection speed. You may need to ensure the local copy is deleted afterwards if it contains sensitive data.

Is it perhaps anything to do with the worksheet being open by another user?

------------------------------
Jonathan Peters
------------------------------