For Each Excel Row

UiPath.Excel.Activities.Business.ExcelForEachRow

Executes one or more activities for each row in a range, table, or sheet. The activity can be used with an Excel file selected for a parent Use Excel File activity, or with the Project Notebook.

Use this activity when you are working with a range or sheet with multiple rows, and you want to repeat one or more activities for each individual row. Set the activities to repeat inside Excel for Each Row and, when you configure the activities, select CurrentRow from the Plus menu to indicate that you want to repeat the actions for each row in the iteration.

For most activities, you must also select a specific column to use from each row. To find out what options are available for specifying the column depending on your range selection, see How Range Selection Works. You can also specify the column using the Indicate in Excel option under CurrentRow. In this case, you can select any cell from the column you want to use directly from the file.

To learn how to use this activity, see:

• Tutorial: Iterating through Rows in a Table.
• Tutorial: Working with Word Automation.

Configuring the Activity

In the Body of the Activity

• For each - Enter the name by which to refer to the current row in the iteration. Using a name that describes the rows in the range makes it easier to identify and select the current row in the iteration when configuring activities added inside Excel For Each Row. For example, for a range with information about a different supplier on each row, you can enter Supplier. The default value is CurrentRow.

  When two Excel For Each Row activities are added one inside the other, if this name is the same for both, only the option for the nearest one appears in the Plus menu for child activities.

• In range - Click Plus on the right side of the field and then, from the menu, select the range, table, or sheet to use, or select Indicate in Excel to open the file and select a range directly from the file. Alternatively, you can select Custom Input to enter a range, table name, or sheet name manually, or Open in Advanced Editor to enter a VB expression.
• Has headers - If selected, it indicates that the first row in the range is a header row. When you then configure the activities you add inside Excel For Each Row, you can easily identify the data to select from the current row by the column headers.
• Save after each row - If selected, the Excel file is saved during project execution after each row in the iteration is processed. If not selected, the file is saved only after all the rows in the iteration are processed. This option is not selected by default.

Properties Panel

Common

• DisplayName - The name displayed for the activity in the Designer panel.

Misc

• Private - If selected, the values of variables and arguments are no longer logged at Verbose level.

Options

• Empty Row Behavior - Select how the automation should behave if an empty row is encountered:

  • StopAfterThreeConsecutiveEmptyRows - Stop iterating after three consecutive empty rows. This is the default option.
  • Stop - Stop iterating through rows.
  • Skip - Skip the empty row and move to the next row.
  • Process - Process the empty row.
• Has Headers - See Has Headers in the body of the activity.
• In range - See In range in the body of the activity.
• Save each row - See Save each row in the body of the activity.

How Range Selection Works

StudioX enables you to define the target range of an Excel For Each Row activity in various ways. The following information describes how different selections for In range influence the range used by the activities added inside Excel For Each Row and the options available for indicating which column to use from the current row.

Entire Sheet

Selection example: [Excel]Sheet1

• Range used: All the rows where data is present in the sheet.

• Current Row options:

  • If Has Headers is selected in Excel For Each Row, the options are the values in row 1 of the sheet.
  • If Has Headers is not selected in Excel For Each Row, the options are the column letters (A,B,C, etc).

Table

Selection example: [Excel]Sheet1!Table1

• Range used: All the rows in the selected table.
• Current Row options: The values in the table header regardless of whether Has Headers is selected or not in Excel For Each Row.

Named Range

Selection example: [Excel]Sheet1!Range1

• Range used: All the rows in the selected range.
• Current Row options:
  • If Has Headers is selected in Excel For Each Row, the options are the values in the first row of the range.
  • If Has Headers is not selected in Excel For Each Row, the options are the column indexes (Column 1, Column 2, etc.).

Unnamed Range

Selection example: [Excel]Sheet1!B3:F10

• Range used: All the rows in the selected range.
• Current Row options:
  • If Has Headers is selected in Excel For Each Row, the options are the values in the first row of the selection.
  • If Has Headers is not selected in Excel For Each Row, the options are the column indexes (Column 1, Column 2, etc.).

Entire Columns

Selection example: [Excel]Sheet1!B:F

• Range used: The rows in the selected columns where data is present in the sheet, including the rows where data is present in other columns than the selected ones.
• Current Row options:
  • If Has Headers is selected in Excel For Each Row, the options are the values in the first row of the columns.
  • If Has Headers is not selected in Excel For Each Row, the options are the column indexes (Column 1, Column 2, etc.).

Entire Rows

Selection example: [Excel]Sheet1!3:10

• Range used: The cells where data is present in the selected rows.
• Current Row options:
  • If Has Headers is selected in Excel For Each Row, the options are the values in the first row of the selection.
  • If Has Headers is not selected in Excel For Each Row, the options are the column indexes (Column 1, Column 2, etc.).

Single Cell

Selection example: [Excel]Sheet1!B3

• Range used: Activities identify a range starting with the selected cell as the upper-left corner and expand the range to the right and down as long as there is data in the sheet. For example, if the selection is Sheet1!B3 and Sheet1 has data up to column AC and row 115, activities inside Excel For Each Row use the computed range B3:AC115.

  If the selected cell is to the right of the last column with data or below the last row with data, only the data in the selected cell is used.

• Current Row options:

  • If Has Headers is selected in Excel For Each Row, the options are the values in the first row of the selection.
  • If Has Headers is not selected in Excel For Each Row, the options are the column indexes (Column 1, Column 2, etc.).

Note: The selection examples mentioned above may differ in StudioX. This depends on whether you set an expression on a property or you view the expression preview.