Finding the Cell range in a For each Row activity

Help Studio
, ,
1

Hello Friends

I have an Excel file.
I want to write some data that I got from a web tool into this excel file.

I need to find the Excel Sheet range in a For Each Row loop.
I tried everything but could not find the excel range of the Current Row ( or Item).

Do you understand what I mean?
During the for each loop, the cell in the iteration has an address, has a range like “C12” or “K55”

I need to write this “C12” range value into a Write Range activity in order to write values into excel.

2

Hi @Saltukhan_Dura

What about the following example?

image
image503×589 21.1 KB

Regards

1 Like
3

Hi @Saltukhan_Dura,

  1. Add a Read Range activity to read the Excel file and store the output in a DataTable variable.
  2. Add a For Each Row activity to loop through the rows of the DataTable.
  3. Inside the For Each Row activity, add an Assign activity to get the Excel range of the current row. You can use the following expression to get the range:
"A" + (row.RowNumber + 1).ToString + ":" + ExcelColumnName(columnIndex) + (row.RowNumber + 1).ToString

In the above expression, replace columnIndex with the index of the last column in the Excel sheet (e.g. 3 for column C) and replace row with the variable name of the current row in the For Each Row activity.The ExcelColumnName function is a custom function that converts column index to column name. You can use the following code for the function:

Function ExcelColumnName(columnIndex As Integer) As String
    Dim dividend As Integer
    Dim columnName As String
    Dim modulo As Integer

    Do
        modulo = (columnIndex - 1) Mod 26
        columnName = Chr(65 + modulo) & columnName
        dividend = Int((columnIndex - modulo) / 26)
        columnIndex = dividend
    Loop While columnIndex > 0

    ExcelColumnName = columnName
End Function
  1. Use the Excel range obtained in step 3 to write data into the Excel sheet using a Write Range activity.For example, if you want to write the value of a variable named value into the cell of the current row and column C, you can use the following expression for the range in the Write Range activity:
range = "C" + (row.RowNumber + 1).ToString

In the above expression, replace row with the variable name of the current row in the For Each Row activity.Set the value of the DataTable cell to the value you want to write into the Excel sheet using the Set Value activity.

Hope this helps!

4

Thank you Fernando. But the value is not unique.

5

Thanks Abhimanyu. There is no Rownumber function in UiPath (for the CurrentRow or item in the foreach).

6

Hi @Saltukhan_Dura

As I shared above, im passing the value of the current row within a for each row, you can pass any dynamic value there

7

Lookup Range activity searches for a certain value in the database.
Sometimes my CurrentRow’s value is null through the for each iteration.

8

You can validate if your current row is empty, if so then omit it, if not then search the value, just add an if condition