UiPath

Reading and writing Excel in UiPath.

In the development of UiPath Studio, we often manipulate Excel.

However, since there are so many activities to manipulate in Excel, and since Excel can also be manipulated by clicking and keyboarding, it is sometimes difficult to know which method to use.

In this article, we will discuss recommended Excel operations, Excel activity list, and frequently used Excel activities such as reading and writing.

This site was created by translating a blog created in Japanese into English using the DeepL translation.

Please forgive me if some of the English text is a little strange

 

Recommended Excel operations

There are two patterns of working with Excel: using activities for Excel, or opening an Excel file and using clicks and keyboard.

Also, there are some activities for Excel that require Excel to be installed and some that do not.

It can be summarized in tabular form as follows.

No Excel operation method Installing Excel A lot of things you can operate. Process runtime stability
1 App integration > Excel  Use a subordinate activity Necessary middle stability
2 System > File > Workbook  Use the activities Unnecessary small stability
3 Open Excel and use the click and keyboard activities. Necessary large instability

 

No1 [App integration > Excel Use a subordinate activity] is recommended because it is stable when running the process and there are a lot of things you can do with it.

No.2 [System > File > Workbook Use the activities] has few operations, so you should use it only when you cannot install Excel.

No3, [Open Excel and use the click and keyboard activities.] This will operate Excel using the click and keyboard activities. It is better to avoid using it.

sea otter-kun
sea otter-kun
In this article, I will explain No.1 [App integration > Excel Use a subordinate activity].

 

List of activities for Excel operations

The following table shows the names of the activities under [App integration > Excel] that can operate Excel and what they can do.

Activity Location Activity Name What you can do with the activities
App Integration > Excel Append Range Adds the information stored in a DataTable variable to the end of a specified Excel spreadsheet.
Close Workbook Closes an opened Excel workbook.
Copy Sheet Copies a sheet from a specified workbook and pastes it to another specified workbook.
Delete Range Deletes a specified range in an Excel workbook.
Excel Application Scope Opens an Excel workbook and provides a scope for Excel Activities. When the execution of this activity ends, the specified workbook and the Excel application are closed.
Get Cell Color Extracts the background color of a cell and saves it as a Color variable.
Get Selected Range Returns the selected range as a String variable.
Get Workbook Sheet Looks for a sheet based on its index and returns its name as a String variable.
Get Workbook Sheets Returns a list of all the sheet names in a workbook as String variables, ordered by their index.
Read Cell Reads the value of an Excel cell and stores it in a variable.
Read Cell Formula Extracts the formula used in the specified Excel cell.
Read Column Reads the values from a column beginning with the cell specified in the StartingCell property field, and stores them in an IEnumerable<Object> variable.
Read Range Reads the value of an Excel range and stores it in a DataTable variable.
Read Row Reads the values from a row beginning with the cell specified in the StartingCell field, and stores it in an IEnumerable<object> variable.
Save Workbook Saves changes to the workbook specified in the WorkbookPath property of the Excel Application Scope.
Select Range Selects a specified range from an Excel spreadsheet, in order to perform further actions.
Set Range Color Changes the color of a specified cell or cell range using a Color variable.
Write Cell Writes a value or formula into a specified spreadsheet cell or a range.
Write Range Writes the data from a DataTable variable in a spreadsheet starting with the cell indicated in the StartingCell field.
App Integration > Excel > Processing Auto Fill Range Takes the formulas defined in the rule range and adapts them to the end range, mimicking the Autofill functionality in Excel.
Copy Paste Range Copies an entire range, including values, formulas, table format, and cell format, and pastes it to a specified sheet.
Execute Macro Execute the macro within a workbook. The Workbook file needs to be a Macro-Enabled Workbook.
Insert/Delete Columns Adds or removes a specified number of columns at a certain position.
Insert/Delete Rows Adds or removes a specified number of rows at a certain position.
Invoke VBA Invokes a macro from an external file containing VBA code and runs it against an Excel file.
LookUp Range Searches in a specified range for the coordinates of a cell with a certain value and returns them as String variables.
Remove Duplicates Range Deletes all the rows that are duplicates in a specified range.
App Integration > Excel > Table Get Table Range Extracts the range of an Excel table from a specified spreadsheet.
Create Table Creates a table from a specified range.
Filter Table Filters a table from a spreadsheet based on existing values from a column.
Sort Table Sorts a table from a spreadsheet based on the values of a column.
Create Pivot Table Creates a pivot table from a specified range.
Refresh Pivot Table Refreshes a specified pivot table.
Delete Column Deletes a table column from a spreadsheet based on its name.
Insert Column Inserts a new column into a table.

 

Activities that must be used

 If you want to use an activity under [App Integration > Excel], place the activity in the “Excel Application Scope”.

Therefore, be sure to use the “Excel Application Scope”.

 

Excel Application Scope  Stteing Item

Setting location Setting item Setting contents
Body
Workbook path The full path of the Excel spreadsheet that you want to use.
Properties Common DisplayName The display name of the activity.
File Password The password required for opening password protected Excel workbooks, if necessary. Only String variables and strings are supported.
Workbook path The full path of the Excel spreadsheet that you want to use.
Edit password The password required for editing password protected Excel workbooks, if necessary.
Option InstanceCachePeriod How long to keep the Excel process alive after all child activities are executed.
MacroSetting Specifies the macro level for the current Excel file.
Visible When selected, the Excel file is opened in the foreground while performing actions on it.
Save changes Automatically saves the workbook on each change caused by an activity.
Create if not exists When selected, if the workbook cannot be found at the specified path, a new Excel workbook is created with the name specified in the Workbook path property field.
Read-only Opens the specified workbook in Read-Only mode.
Output Workbook The entire information from the Excel spreadsheet stored in a WorkbookApplication variable.
Use Existing Workbook ExistingWorkbook Use the data from an Excel file that was previously stored in a WorkbookApplication variable.
Misc Private If selected, the values of variables and arguments are no longer logged at Verbose level.

 

Sample Process

penguin-san
penguin-san
If you use the [System > File > Workbook] activity, you do not need the Excel application scope.

 

Frequently used activities

Copy Sheet

To copy a sheet in Excel, use “Copy Sheet”.

Copy Sheet  Setting item

Setting location Setting item Setting contents
Properties Common DisplayName The display name of the activity.
Destination DestinationFilePath The workbook where you want to copy the sheet.
DestinationSheetName The name you want to use for the pasted sheet.
Input SheetName  The name of the sheet you want to copy.
Misc Private If selected, the values of variables and arguments are no longer logged at Verbose level.

 

Sample Process
Copy the specified sheet to create another sheet.

・Copy Sheet  Properties

 

・Target Excel file

・Excel file after execution

sea otter-kun
sea otter-kun
After execution, the sheet specified in [SheetName] of the “Copy Shee” property will be duplicated with the name specified in [DestinationSheetName].
penguin-san
penguin-san
If [DestinationFilePath] of the “Copy Sheet” property is not specified, the sheet will be duplicated in the same file.

 

Write Cell

To write to an Excel cell, use “Write Cell”.

Write Cell  Stteing item

Setteing location Setteing item Setting contents
Body
SheetName The name of the sheet in which the cell or range of cells that you want to write to is.
 Range The Excel cell or range that you want to write to.
Value The value or formula that you want to be written to a cell or range.
Properties Common DisplayName The display name of the activity.
Destination Range The Excel cell or range that you want to write to.
SheetName The name of the sheet in which the cell or range of cells that you want to write to is.
Input Value The value or formula that you want to be written to a cell or range.
Misc Private If selected, the values of variables and arguments are no longer logged at Verbose level.

 

Sample Process
Reads an Excel file and writes to the specified cell.

・Target Excel file

 

・Excel file after running the process

 

Read Cell

To read a cell in Excel, use “Read Cell”.

Read Cell  Stteing item

Setteing location Setting item Setting contents
Body
SheetName The name of the sheet in which the cell that you want to read is.
Cell The cell that you want to read.
Properties Common DisplayName The display name of the activity.
Input SheetName The name of the sheet in which the cell that you want to read is.
Cell The cell that you want to read.
Output Result  Stores the information from the specified spreadsheet cell in a variable.
Misc Private If selected, the values of variables and arguments are no longer logged at Verbose level.

 

Sample Process
Load an Excel file and read the specified cells.

・”Read Cell A1″ Properties

・”Read Cell B2″  Properties

 

・Target Excel file


 

・Execution result

 

Write Range

To write to a range in Excel, use “Write Range”.

Write Range  Setteing item

Setting location Setteing item Setting contents
Body SheetName The name of the sheet in which the range that you want to write to is.
StartingCell  The cell from which to start writing the data.
Properties Common DisplayName The display name of the activity.
Destination SheetName The name of the sheet in which the range that you want to write to is.
StartingCell  The cell from which to start writing the data.
Options AddHeaders When selected, column headers are also written to the specified range.
Misc Private  If selected, the values of variables and arguments are no longer logged at Verbose level.

 

Sample Process
Write a range of data tables read from a CSV file to an Excel file.

・Write Range Properties

・CSV file to be read

・Excel file to be read

・Excel file after running the process

 

Read Range

To read in an Excel range, use “Read Range”.

Read Range  Setteing item

Setting location Setteing item Setting Contens
Body
SheetName The range that you want to read.
Range The name of the sheet in which the range that you want to read is.
Properties Common DisplayName The display name of the activity.
Input SheetName The range that you want to read.
Range The name of the sheet in which the range that you want to read is.
Options AddHeaders When selected, the column headers from the specified spreadsheet range are also extracted.
PreserveFormat
(slower)
 Selecting this check box keeps the formatting of the range that you want to read.
UseFilter When selected, the activity does not read what has been filtered out of the specified range.
Output DataTable Stores the data extracted from the specified Excel range in a DataTable variable.
Misc Private If selected, the values of variables and arguments are no longer logged at Verbose level.

 

 

Sample Process
Log output of the data table read in a range of Excel files, one line at a time.

・Read Range  Properties

・For Each Row in Data Table  Properties

 

・Variables

 

・Excel file to be read

・Execution result

For more information on how to use “For Each Row in Data Table”, please refer to the following article.

https://penrako.com/uiparhloop/#rtoc-4

https://penrako.com/uiparhloop/#rtoc-4(別ウインドウ)

 

Append Range

Use “Append Range” to append in a range after the last line of the Excel file.

Append Range  Setting item

Setting location Setting item Setting Contens
Body
SheetName The name of the sheet in which to add the specified range.
DataTable The data to be added.
Properties Common DisplayName  The display name of the activity.
Input SheetName  The name of the sheet in which to add the specified range.
DataTable The data to be added.
Misc Private  If selected, the values of variables and arguments are no longer logged at Verbose level.

 

Sample Process
Read the CSV file, output it to a data table, and append the output data table to the end of the existing Excel file.

・Append Range  Properties

 

・CSV file to be read

 

・Excel file to be read

 

・Excel file after running the process

 

Summary

  • For Excel operations, it is recommended to use the activities under “App Integration > Excel”.
  • If you want to use an activity under “App Integration > Excel”, place the activity in the “Excel Application Scope”.
  • The most frequently used activities are Copy Sheet, Write Cell, Read Cell, Write Range, Read Range, and Append Range.