Important! Read this.
We have launched a new documentation website. If you're using Enterpise Edition 10 (or higher) or Express/Business Edition 2.4.1 (or higher), visit this page.
We have moved to a new documentation platform. This section is no longer supported. For the up-to-date information, see Excel automation best practice.
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.
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.
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.
To start work with Excel files, you need to use the Open Spreadsheet action. All other Excel actions should be nested under Open Spreadsheet.
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.
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.
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 Row, Get Column, Get Range, and Set Range. See several tips on how to use them below.
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.
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.
See some samples where built-in Excel actions are used:
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:
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.
There are two options to open an Excel file.
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.
If the path to the file contains spaces, you need to open it via the Run dialogue.
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.
See a couple of samples where the desktop approach is used: