Skip to end of metadata
Go to start of metadata

The Excel action group is intended for automating Excel spreadsheet manipulations, such as getting/setting cell values, switching between sheets, saving, etc. These actions are NOT automatically recorded when you work in Excel during a recording process.

All Excel Actions are executed in the background, so the application window does not appear on the screen.

The main benefits of using Excel actions are as follows.

  • Stability – system actions are more reliable and quicker than image-based ones.
  • Speed of execution – bot execution is done in background when Excel application window is not visible. RPA Recorder only displays the step number, name, and its result (success/failure).
  • No need for active Window – you do not need to put Excel actions inside a Window action.
  • Readability – even new users will understand the action flow at first glance.

All Excel Actions can only be used, if your spreadsheet is opened by the Open spreadsheet action. If you open the spreadsheet with the Launch Application or Enter Keystrokes actions and add the Excel actions to the recording, these actions will be skipped during playback or execution in Control Tower.

Preconditions

While a bot is executing Excel actions, the following rules should be observed.

  • Excel actions are limited to .xls and .xlsx file types only.

    Files of other types, for example, .csv or .xml, are not supported. To work with them, either convert to .xls and .xlsx, or open a file manually and grab the data by copying and pasting to clipboard, and then save to a variable.

  • An Excel file should not be used by other program (opened in Excel editor or other application).
  • For files with Protected View, only read operations are supported. Click Enable Editing to write information (set cell value, delete cell).

Coordinates and Position

To perform cell operations (get/set/delete value, get row/column), a bot can use either cell coordinates (A1, BQ503) or cell position (start of column, cell above, etc.) as a unique cell identifier. The position is counted starting from the currently selected cell (D7 on the example below).

#Position optionsPreview

Current (current cell)

1

Start of document

2Start of column
3Start of row
4End of column
5End of row
6Cell above
7Cell below
8Cell to the left
9Cell to the right
  • Only Set Cell Value and Delete Cell actions modify the current position.
  • If you save a spreadsheet with the D7 cell selected, next time you open the spreadsheet, the position will be calculated starting from the D7 cell.

Loops and Conditions

When working with a spreadsheet you can use Loops and Conditions to repeat the Excel actions (for example, Set Cell Value) as long as it is defined by a Loop or Condition.

Excel Action with LoopExcel Action with Condition

Some actions cannot be added within Condition under Open Spreadsheet

As all Excel actions are executed in the background, you cannot add the following actions under a Condition or Loop nested in the Open Spreadsheet section:

  • Application
  • Files and Folders
  • Mouse
  • Web
  • Enter Keystrokes
  • Clipboard
  • OCR
  • Custom Action

Use Case

You can use Excel together with Loops to iterate through Excel documents, extract data from them, and paste into your document.

Excel Actions

Open Spreadsheet

The action opens a specified Excel spreadsheet (.xls or .xlsx) in background for further data manipulations.

  • Open Spreadsheet should contain other Excel actions as its children (Switch To Sheet, Get Cell Value, Delete Cell, etc.) – just drag them onto the Open Spreadsheet action.
  • The spreadsheet file will be automatically closed after the bot executes the last child action of the Open Spreadsheet action.
  • The bot can manipulate one spreadsheet at a time, therefore Open Spreadsheet actions cannot be nested inside each other.

Parameters

  • File location – the path to the spreadsheet that will be opened. You can type the path or click the Browse button and browse to it in the file explorer.
  • Save file after last action – eliminates the need to create an additional save action at the end of each excel block.

Save Spreadsheet

The action saves a currently opened spreadsheet to the same or to a new file.

The Save Spreadsheet action can be avoided if the Open Spreadsheet action has the Save file after last action flag enabled.

Parameters

  • Save type
    • Save to the same file – the currently opened file is saved with all the previous changes.
    • Save as a new file – on clicking Save as new, all actions are performed in a new file, the initial file is closed without saving.

  • Destination folder – the path to the folder where a spreadsheet will be saved. You can type the path or click the Browse button and browse to it in the file explorer.
  • File name – a full name for a new spreadsheet.

Switch to Sheet

The action switches to a specific sheet in the Excel file (makes it active) by its name or order number.

Note that Excel file is opened on the last active sheet (where the last save was made).

Parameters

  • Specify index – the number of the sheet you want to switch to. Numbering starts from 0.
  • Specify name – the name of the sheet you want to switch to.

Set Active Cell

The action allows to choose a cell for the following manipulations or actions using coordinates or position.

You can use the action to start your iteration through the spreadsheet from a defined cell, as the MS Excel saves the selected cell, and it can differ from the one you want to start with.

Parameters

  • Coordinates/Position – see the Position description.

Set Cell Value

The action sets value for a specific cell by coordinates or position.

Parameters

  • Coordinates/Position – see the Position description.
  • New cell value – a Recorder variable of the String type that will be copied to the target cell.

Get Cell Value

The action gets value of a specific cell and sets it to a variable for further usage.

Parameters

  • Coordinates/Position – see the Position description.
  • Save cell value to variable – the target cell value will be copied to this variable.

Starting from RPA Express 2.2.0, the Get Cell Value action is enabled to work for calculated values (formulas).

Delete Cell

The action deletes value of a target cell.

Parameters

  • Coordinates/Position – see the Position description.

Get Row

The action gets value of a specific spreadsheet row on an active sheet and sets it to a List variable for further usage.

Parameters

  • Index – number of row (1, 2).
  • Position –  the position of the row related to the currently selected cell.
  • Save result to list variable – the target non-empty row values will be copied to this variable. Only variables of the List type are supported.

Get Column

The action gets value of a specific spreadsheet column on an active sheet and sets it to a List variable for further usage.

Parameters

  • Index – the column index (A, F, BB).
  • Position – the position of the column related to the currently selected cell.
  • Save result to list variable – the target non-empty column values will be copied to this variable. Only variables of the List type are supported.

Get Range

The action gets values from cells of a specific spreadsheet range on an active sheet and copies them to a Table variable for further usage.

Parameters:

  • From cell # – the start cell of the range. It can be defined either explicitly – F5, or using a Recorder variable of Text type – ${start_cell}.
  • To cell # –  the end cell of the range. It can be defined either explicitly – H20, or using a Recorder variable of Text type – ${end_cell}.
  • Save the selected to Table variable – the values from the range, including cells without values, will be copied to this variable. Only variables of the Table type are supported.
  • If From cell # is empty, the selection starts from the first top-left cell (A1).
  • If To cell # is empty, the selection is made to the last bottom-right non-empty cell.

  • Empty spreadsheet cells are copied as empty values.
  • If From > To (start and end cells), the cells are swapped to make the selection, for example:
    • C5:A2 == A2:C5
    • D37:H27 == D27:H37

Set Range

The action gets values from a Table variable and copies them to cells of a defined spreadsheet range on an active sheet.

Parameters

  • Input: Select Table variable – select a Table variable to copy the values from and insert them to the range on the active sheet.
  • From cell # – the start cell of the range. It can be defined either explicitly – A2, or using a Recorder variable of Text type – ${start_cell}.
  • To cell # –  the end cell of the range. It can be defined either explicitly – E20, or using a Recorder variable of Text type – ${end_cell}.
  • If From cell # is empty, the insertion starts from the first top-left cell (A1).
  • If To cell # is empty, the whole content from the Table variable will be inserted to the spreadsheet.
  • If From > To (start and end cells), the cells are swapped to make the selection, for example:
    • C5:A2 == A2:C5
    • D37:H27 == D27:H37
  • If the number of columns or rows in Excel Range is less, than the Table variable contains, the exceeding columns/rows are trimmed and not pasted. Alternatively, when the number of columns/rows in Excel Range exceeds the number of columns/rows in the Table variable, the Table content is inserted, and the other cells in the Range remain empty.
  • No labels