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.

 

 Related Articles Learn the Creation Techniques f UiPath robotics creation with Udemy’s online courses that take it up a notch

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.

 

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

 

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

 

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.

 Related Articles For Each Row

 

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

Back to Table of Contents

 

 Related Articles Learn the Creation Techniques f UiPath robotics creation with Udemy’s online courses that take it up a notch

 same category UiPath