Skip to end of metadata
Go to start of metadata

The most popular desktop application to be automated with the help of Intelligent Automation Cloud – Express or Business – is Microsoft Excel. There is a specific actions group in Actions Library to work with Excel files. Here you can find some recommendations on how to work with them in your own scripts.


Different approaches to automating Excel

There are two different approaches on how to work with Excel files in Intelligent Automation Cloud:

For some cases, you need to follow one of these approaches, and below you will find several examples when you can use them.

Excel as a separate system

Automating Excel as a separate system means using specific actions that work with files of .xls and .xslx types. If you need to automate other file types like .csv, .xlsm, etc., you need to follow the other approach.

A specific feature of this approach is that Excel actions are executed in the background and you will not see anything on your screen. The main advantage is that scripts using Excel actions work a lot faster than the ones using the desktop approach and are executed in the background, so it is better to use this approach when you have to process a large amount of data.

Using built-in Excel actions

Opening Excel files

  • Before working with an Excel file using Excel actions, make sure that your file exists in the required path. The Excel file needs to be closed when the bot works with it.

    Supported Excel file types

    Remember that only .xls and .xlsx file types are supported in Excel actions. 

  • Make sure you have provided the correct path to your Excel file via Recorder Variables or directly in the action. To get access to the Excel file on an external drive, use the full file path. You can find it in the Location file property on the General tab.
  • To start work with Excel files, you need to use the Open Spreadsheet action. All other Excel actions should be nested under Open Spreadsheet.

    • You cannot use Excel actions outside of Open Spreadsheet.
    • Some actions like WebOCR, Application, Mouse and some others cannot be nested under Open Spreadsheet.
    • You cannot use the nested Open Spreadsheet within another Open Spreadsheet
  • In case you have two and more sheets in your Excel file, it is good practice to define the active sheet that will be processed by your script. You can do it via the Switch to Sheet action. You can switch to multiple sheets under one Open Spreadsheet action.

Working with Excel elements

After opening the file, you can add other nested Excel actions to process data in Excel. They depend on what exactly you need to do with Excel files. See common steps below.

Working with separate cells

  • To get value from a specific Excel cell, use the Get Cell Value action.

    In case of a formula, the action returns the formula result as a text. If you need to get the formula text, use the other approach.

  • Variables store values from Excel as text. If an Excel cell contains a number, for example, you can to convert the text value back  number using the Number Format action.
  • To enter a value into an Excel cell, use Set Cell Value

Working with groups of cells (rows, columns, and ranges)

In most cases, Excel data is being used not as one-cell value, but as a group of structured values – rows, columns, and tables. To work with such data, there are appropriate actions: Get RowGet ColumnGet Range, and Set Range. See several tips on how to use them below.

  • For processing ranges with several rows and columns, save the data in a Table variable using Get Range.
  • There are no specific actions to write values into a separate column or row. There are 2 approaches you can use to do it:
    • If you know coordinates of the first and last cell where you need to paste the data:  write the data in a Table variable and use Set Range with exact start and end coordinates to write it to Excel.
    • If you don't know the coordinates of the first and last cell where you need to paste the data:  save the data in List file and write to Excel using For Each Loop and Set Cell Value.
  • To copy a large amount of data from Excel to another Excel file, use Get Range / Set Range actions.
  • To change data in Excel, use Get Range to read it to a Table variable, process variable in your script, and use Set Range to enter the data back from the variable to the Excel sheet.

Working with formulas

Since version 2.2.0, Excel actions can work with Excel formulas.

There is no automatic recalculation of a formula cell value after changing the input data. To recalculate the data in Excel formulas after changes, use Get Range (or Get Cell Value for the only cell) for cells with formulas before closing an Excel file.

Saving and closing Excel files

An Excel file is automatically closed after script execution is done.

It is saved by default, so to be sure that all the data is saved, add Save Spreadsheet action at the end of your script. The second option to save data is using the Save file after last action option in Open Spreadsheet.

Automation samples

See some samples where built-in Excel actions are used:

Excel as a desktop application

Another approach to work with Excel is automating it as a desktop application. This approach can be applied to other desktop application, not only to Excel. Advantages of this approach allow:

  • to use native Excel functionality like filtering, coloring cells, changing format, etc.
  • to use Excel keyboard shortcuts
  • to use other actions from Actions Library, like Mouse actions, Application actions, etc.
  • to work with password-protected Excel files and other Excel file types like .xlsm, .xml, etc.
  • to observe on the screen how your bot works with a file

Using the desktop approach

To automate Excel as a usual desktop application, you need to use object selectors and key shortcuts, as for other desktop applications.

Object selectors can differ in various Excel versions, so we recommend using the same Excel version (for example, MS Excel 2016) in both the test and the production environments.

Opening Excel files

There are two options to open an Excel file.

  1. If you have the full path to your file, you can use Win + R and open it with the Run command.

    The steps in Actions Flow can look like as on the screenshot below.


  2. You can use the Launch Application action and specify the path to the Excel.exe and the path to the file in the Executable file or command field.

If the path to the file contains spaces, you need to open it via the Run dialogue.

Working with Excel elements

In the case of opening the Excel file on the screen, you can use other actions from Actions Library to automate it, except for some specific actions, like Excel or Web actions. To work with cells, you need to use object selectors with the Window control option. See several tips below.

  • If you work with several Excel files simultaneously, you need to define the required file window via the Window action.
  • To get a value from the specific cell, click on it using the object selector, copy it using Ctrl + C and save to a Recorder Variable using the Clipboard action. The script example can look like as follows.
  • To set a value to the specific cell, click on the required cell and enter the variable value using the Clipboard action and Ctrl + V key combination.
  • To move between cells in an Excel sheet, use the Up/Down and Left/Right arrows key shortcuts. See more Excel shortcuts here.

Saving and closing Excel files

  • To save the file after changing it, you can use either key shortcuts like Ctrl + S or object selectors. For example, in the script below, the bot saves the file via the Excel Save button. To close the file, press Alt + F4

  • If you use Launch Application to open the Excel file, it will be closed automatically at the end of the script execution.
  • If you open the file via the Run dialogue,  you need to add actions to close it at the end of the script. To close the file, you can press Alt + F4 or use Close Window action.

Automation samples

See a couple of samples where the desktop approach is used:

  • No labels