Automating Excel activities using UiPath

Excel activities in UiPath are available as two separate packages, which are available under System > File > Workbook and the other is available under App integration > Excel. Both packages same several similar functionalities but the Excel System package works independently while Excel App integration requires Excel application scope.

For example, the Read Range activity under the Excel System package only requires the sheet name (Sheet1), Cell Range (A1: A2), and the location of the file.

Whereas, Read Range under Excel App integration requires the Excel Application Scope, where the location of the file requires to be mentioned. This is beneficial when there are a lot of Excel App integration activities to be added and only once the Excel file location requires to be supplied instead of doing the same multiple times.

As evident from the image, Read Range activity under Excel App integration only requires the Sheet name and range.

But there is a major difference between Excel App integration and Excel System Workbook in terms of their operation. Excel App Integration requires MS Excel to be installed on the local machine and opens Excel in the background to perform the required task. Due to this, it can provide a lot more complex functionalities such as Set Cell Colour, AutoFill, etc.

The image highlights all the Properties under the Excel Application Scope.

Properties of Excel Application Scope

• EditPassword
  • This allows the user to input the password required for editing the workbook
• Password
  • This is useful for opening password-protected workbooks
• WorkbookPath
  • The location of the workbook file is supplied in this field.
• Autosave
  • Automatically saves the workbook file after each iteration.
• ReadOnly
  • Sets the workbook to read-only and prevents editing or write/append operations
• Visible
  • Turning this off makes UiPath open the workbook in the background.
• Workbook
  • A Data Table variable requires to be supplied where the Excel information will be saved.
• MacroSetting
  • When ticked enables all Macros associated with an Excel File and also allows the user to execute them.

Read range

This Activity is used to read data from an Excel Sheet into a Data Table. The important properties of this activity include AddHeaders which considers the first row as Column Names.

We can output the read Excel file using a Message Box but before that, we need to convert the Data Table to an Excel file using Output Data Table activity. In the Output Box, we are supplying a string variable named Table_to_string.

Using a Message Box we can show the output result of Read Range activity like this.

Read Cell Formula

This is used to read the formula within a cell and has both Excel System Workbook activity as well as in Excel App Integration. However, in many cases, the activity from Excel System Workbook fails to read the cell formula, and hence it is advisable to use the activity under the Excel Application Scope.

The Final result is stored inside a String variable and supplied within the Result Box

Lookup Data Table

This is a Data Table activity but works well to Find required values from information read from an Excel file using Read Range. This performs exactly similar to VLookUp for Excel and has several properties to fine-tune it for a specific need.

Input

• DataTable – The user needs to supply the Data Table variable within which the lookup will be performed.
• LookupValue – The value that is to be searched within the Data Table

Lookup Column

• Column – A variable that points to the column which contains the value according to which a search is made
• ColumnIndex – The index of the column, which then contains the keyword that is to be searched within the inputted Data Table
• ColumnName - The name of the column which points to the value that will be searched within the supplied Data Table

It is important to note that only one Lookup Column value requires to be filled or can be filled.

Output

• CellValue – This stores the found data into a string variable
• RowIndex - This stores the row number of the found information into the Int32 variable

Target Column

• Column – A variable that points to the column containing the information that is being searched
• ColumnIndex – The index of the column, where the required information is located. For example, the index for Column A is 0, and then 1 for Column B and so on. This is different than Excel where the first column index is represented with 1.
• ColumnName - The name of the column where the lookup value is present

Execute Macro

This activity can only be used using the Excel Application Scope and allows to execute of a Macro in Macro-Enabled workbooks. In the Macro name, the user needs to input the exact name of the Macro as it is saved within the Excel workbook.

This is the Properties Panel to Execute Macro activity. The Macro Output can be saved inside a variable as well as several Macro Parameters can be supplied as per requirements.

Auto Fill Range

This allows the user to perform Auto Fill much similar to what is available inside Excel. To perform this, the user needs to point to the Source cell/range using and state it in the first box. For example, the Source range could I2: J2 and also simply I2. The destination range is the up to where the AutoFill will be dragged onto. This could be I20: J20 or simply I20 as per requirements.

It is important to note that this activity only works under Excel Application Scope. The Sheet name can be supplied from the Properties Panel.