Please help with reading specific range without sheet number

Help Activities
1

Hi, any idea how to read specific cell range in all sheets and add them together? Basically just like read range without indicating the sheet number. Thanks

2

Duplicate of:

In general it could start like this:

grafik
grafik558×586 11.5 KB

retrieve all work sheets
iterate over the found worksheets
read in Excel worksheet

3

Hi, I got this error.

For Each: Object reference not set to an instance of an object.

Thanks for replying my last comment

4

ensure

  • type argument from for each is set to string
  • variables are correct defined and used e.g. WorkSheets is the output of get WorkBook Sheets activity
1 Like
5

image
image1904×723 65 KB

Hi sorry i have been trying after you sent that message, but still can’t seem to get it, I used these variables

6

looks like WorkSheet is of wrong datatype
check the variable panel ensure Worksheets is of Datatype: List(Of String)

7

If i change to this type of variable, I cant use the same variable to output in read range

8

not clear what is meant by this, but you should be very close to target

  • for each item - WorkSheets (the output of get Workbook Sheets)
    let you iterate over all Worksheets

  • with read range you read in the excel data of looped worksheet name

  • you can write the datatable directly to another worksheet
    OR

  • merge the different datatable to a single one
    OR
    Add it to a list of datatables and process it afterwards

Also do not miss out to go to the UiPath Academy and check out some courses on the essentials of UiPath

9

image
image1780×770 51.9 KB

Hi, somehow my Datatable is empty still, all the sheets do not get read.

Thanks for helping me thus far, really stuck with this.

10

ensure that the scope of datatable variable is set to a higher level
kindly note:
current implementation is transporting only last read range result datatable. Please reread the hints for this:

11

By the scope of DataTable set to a higher you mean B1:B10? As I only want numerical values (all in one column) and the end of my values stop at B6. Still trying to figure out why datatable is empty.

12

grafik
grafik1435×507 61.3 KB

Do not miss out courses from Academy on the basics

13

image
image1835×700 54 KB

Hi, the error still occurs. Restarted my UIpath, still no values

14

Hi @jing_jing

Seems like your DataTable was refresh everytime because your Build DataTable activity is inside the For Each activity loop. Consider placing the Build DataTable activity before the For Each activity loop.

15

Hi, any idea how to read all 3 sheets(in my case) into 3 different datatables?
I have tried 3 read ranges but somehow there is some duplication of data (Meaning 2 values copied in Data Table are the same ) , or even getting the same value for all datatables

16

Ok thanks!This worked for me