What is the definition of the "whole worksheet" when I use read range on an Excel worksheet


In the documentation on “Read Range” for Excel it is stated that “If the range isn’t specified, the whole spreadsheet is read.” But what does “the whole spreadsheet mean?”

I have a spreadsheet with 877 rows (see picture). My BOT must find the last empty row, which is 878, and paste some data there. There are some empty rows above this last one, as you can see. Some of the cells have formulas in them, and this formulas are auto-filled down to row 2000 or so.

When I do a read range (with blanks in the range) it is bringing back many more rows than 877, and then there is an append range with a data table and the data is pushed way down the spreadsheet. So this makes me question what read range considers the “rest of the worksheet”. Can’t be where there is no data at all because there are no rows with no data sprinkled throughout the sheet. Do formulas / formatting count?

The read range had “preserve formatting” checked previously. This slows things down, and I don’t think I care about formatting, but maybe this is essential to get the right number of rows?

Any help would be appreciated.

Hi @Bryan_Schmiedeler !
→ What is the exact number of rows that the bot reads in your datatable ? Do you mind, after using a read range, to drag and drop a writeline activity and write name_of_datatable.rows.count.tostring and let us know the number ?
→ If you use read range only to get the number of rows, then you don’t need to check preserve format

Hiba,

Thank you for your reply.

I noticed in the code (in at least one activity) were were not reading a range before doing the append range, which I thought was very odd. So I added in a read range to a test data table and then did a count.

The value I got was 877, which is correct. That was the last row with data; we needed to write at the next row, 878.

So then I let the BOT append the range, and checked and it appended it at row 9351!

Between the first read range and the output of 878 there is some other code - I will check and see if this did something that could cause this problem.

Still, I am left wondering what UIPath means when it says “the rest of the spreadsheet.” We are going to use read range and append range a lot, and I want to make sure that a) we do not write over any data and b) we don’t leave huge gaps between where we write. It may be that we will want to write something to find the last row (which worked above when we did a read range - it gave us 877) and store that and then do a write range or append range but put the next cell “A877” into the correct parameter.

Hi @Bryan_Schmiedeler
I encountered your issue when I had formulas in my excel file. Turns out that the bot reads until the last row where the formula is. :grin:
What I would recommend you then is to use Write Range activity, and at the property “starting cell” you may write “A” + (name_of_datatable.rows.count +1).tostring then it will write in the wanted place.
Don’t worry: the bot does not chose arbitrarily the place where the datatable is written, there must be something about the structure of the file. If there are formula after the 878th row then an append range is not compatible. Check also if you don’t have data written for unknown reasons at further columns (like AA etc): to do so I suggest to unzoom your excel file