Read Range: Index was out of range. Must be non-negative and less than the size of the collection.Parameter name: index

Help Studio
1

I am getting error “Read Range: Index was out of range. Must be non-negative and less than the size of the collection.Parameter name: index” while reading excel file using workbook read range. Tried without giving any range and also by “A1”. nothing worked

2

Hi @Sanchit7

Is the excel sheet able to view ?

Thanks
Ashwin S

3

Hi @Sanchit7

Index was out of range usually it will give error when your sheet name or the range of the cells are given wrong, it is better to show what you are trying to automate, so that we can check

Thanks

4

Yes, when I use Read Range withing excel application scope, the bot stays in running mode and never stops nor gives any error. When I use workbook read range, it says index was out of range. Please help me

5

Hi @Sanchit7 ,

I do facing the same issue. Have you found the solution?. If you have, please share here. It will be useful for us too.

The excel sheet name and range are correct. But i still get this error. When i go with excel application scope’s read range activity, it works fine. But i want to use workbook’s read range activity. any idea?

Thanks in advance!

6

Hi @Gopi_Arumugam

i also faced this issue in the past , can you check your excel whether you are having negative values (negative values in the sense excel specifically some values these values are little strange ,In normal excel file values will be like below
Username
John
Abrham
King

in these files values will be like these
Username
john
-000000.18978900982
king
-000000.18907897693

if the above mentioned values are there you need to ignore that particular column range and read remaining range which you want

7

Hi @amalmarella ,

Thanks a lot for your response. I don’t have any values like these in excel sheet. I found another scenario, The excel contains four sheets. In that one is pivot table. When i remove that pivot table sheet and try to read other 3 sheets. then it works. What could be the issue? But the pivot is not in the sheet which i want to read. Its a separate sheet.

2 Likes
8

The cells should have value. If excel does not have any detail so that it can give error. And please use just double quota -“”- like this read full range.

9

Hi @Gopi_Arumugam

it was good you identified it was causing due to pivot table , for that check with the person who created the pivot table , for every pivot table there will be reference sheet like when ever data in that sheet refreshed then pivot table also refreshes by itself. you need to check those references and data connections are given correct, try with multiple sample Input files

10

you might want to try this on a assign activity if(Variable_Name.IsNullOrEmpty,0,Variable_Name) in this way you’ll have checking if a cell is empty then it’ll replace into zero. If not, it’ll remain as it is. Tell me if it works

11

Thank you for sharing this! I was going crazy trying to figure it out and the only thing that had changed was two new sheets - with one being a pivot.

Thanks!