Open Excel and assign each cell to a variable from multiple rows

Help Studio
1

Hi,

I have an Excel spreadsheet containing 4 columns and multiple rows, the row range could increase/decrease but the columns will remain static.

What is the best way to loop through all rows and assign each column value to a variable (4 separate variables in total) please?

1 Like
2

Hi @TRX,

You could use some excel activity such as read range to read all your excel and save the data into a DataTable. Then you could use For Each Row activity to loop through all the rows in your datatable, to access the value from the row you could use row.item(“Column Name”).ToString.

Hope this helps! =)

2 Likes
3

  1. Read data to a datatable.

  2. For Each Row Activity On Datatable:

variableA = row.item(“column1name”).tostring
variableB = row.item(“column2name”).tostring
variableC = row.item(“column3name”).tostring
variableD = row.item(“column4name”).tostring

  1. Do something with each variable.

  2. Next Row

3 Likes
4

Hey man.

Ok no problem.

Use an excel scope activity to open your excel spreadsheet and pass in your excel full path. Then use a read range activity to output your datatable. In order to access the values in the columns. You need to use a for each row activity. This will be use to cycle through all the rows. Add your datatable as input for each row. Then to access the values in the datatable, use item(0).toString. 0 will get you the first column in the datatable. You can then show this result using a message box or write line activity. Next you can store this expression in a string called column1 or something. Repeat this process for other 3 columns.

I hope this helps. :grin:

2 Likes
5

Thanks guys! I will give it a go :slight_smile:

1 Like