Manipulate Excel with Modern Activities using UiPath

This article describes how to work with Excel in Modern Activities.

Proceed to Table of Contents

Modern and Classic Activities

UiPath 2023.6 is configured by default to operate on modern activities in Excel.

If you want to use classic activities, check the “Classic” checkbox under “Filter Criteria” for the activity.

Excelのモダンとクラシックアクティビティの違い

クラシック(英語)	モダン(英語)
Excel Application Scope	Use Excel File
Excel Process Scope	Excel Process Scope
–	Single Excel Process Scope
Append To CSV	Append To CSV
Read CSV	Read CSV
Write CSV	Write CSV
Delete Column	Delete Column
Filter Table	Filter
Get Table Range	–
Insert Column	Insert Column
Sort Table	–
Append Range	Append Range
Close Workbook	–
Get Cell Color	–
Read Cell	Read Cell Value
Read Cell Formula	Read Cell Formula
Read Column	–
Read Range	Read Range
Read Row	–
Select Range	–
Set Range Color	–
Write Cell	Write Cell
Write Range	Fill Range
Save Workbook	Save Excel File
	Save Excel File As
Create Table	Create Table
Get Workbook Sheet	–
Get Workbook Sheets	–
Refresh Pivot Table	Refresh Pivot Table
Create Pivot Table	Create Pivot Table
–	Filter Pivot Table
–	Change Pivot Data Source
Get Selected Range	–
Copy Sheet	–
Delete Range	–
Auto Fill Range	Auto Fill
Copy Paste Range	Copy Paste Range
Execute Macro	Run Spreadsheet Macro
Insert/Delete Columns	Delete Column
Insert/Delete Rows	Delete Rows
–	Insert Rows
Invoke VBA	–
LookUp Range	–
Remove Duplicates Range	Remove Duplicates
–	Autofit Range
–	Clear Sheet/Range/Table
–	Copy/Paste Range
–	Export to CSV
–	Delete Sheet
–	Duplicate Sheet
–	Find First/Last Data Row
–	Find/Replace Value
–	For Each Excel Row
–	For Each Excel Sheet
–	Format As Table
–	Format Cells
	Get Excel Chart
–	Insert Chart
	Update Excel Chart
–	Insert Sheet
–	Match Function
–	Protect Sheet
–	Refresh Excel Data Connections
	Save Excel File
–	Rename Sheet
–	Save Excel File As PDF
–	Sort Range
–	Text to Columns
–	Unprotect Sheet
–	Update Excel Chart
–	VLOOKUP
	Clear Sheet/Range/Table
–	Write DataTable to Excel

Manipulation of Excel files

To work with Excel files in a Modern Activity, use “Use Excel File” within the “Excel Process Scope” or “Single Excel Process Scope” to specify the Excel file.

Excel Process Scope

Excel Process Scope setting items

Setup Location	Setting items	Configuration details
Common	DisplayName	The display name of the activity.
Misc	Private	If selected, the values of variables and arguments are no longer logged at Verbose level.
Options	Display alerts	If selected, Excel can display alerts and messages.
	Existing processes action	The action to be executed if other Excel processes are running.
	File conflict resolution	The action to be executed if Excel file conflicts are detected between Excel processes.
	Launch method	Determines whether an Excel process is launched via COM APIs or as full process.
	Launch timeout	The time to wait for Excel to start in seconds, if launched as a full process.
	Macro settings	Specifies the macro level for the current Excel file.
	Process mode	Determines how the Excel Process Scope activity manages Excel processes:
	Show Excel window	If selected, Excel windows appear during the automation.

Single Excel Process Scope

Single Excel Process Scope　setting items

Setup Location	Setting items	Configuration details
Common	DisplayName	The display name of the activity.
Misc	Private	If selected, the values of variables and arguments are no longer logged at Verbose level.

Use Excel File

[jin_icon_informationUse Excel File setting items

Setup Location	Setting items	Configuration details
Common	DisplayName	The name displayed for the activity in the Designer panel.
File	Edit password	The password required for editing the Excel workbook, if the file is password-protected.
	Password	The password required for opening the Excel workbook, if the file is password-protected.
	Workbook path	The path to the Excel file. If the file does not exist and you select the CreateNewFile option, StudioX creates the file.
Misc	Private	If selected, the values of variables and arguments are no longer logged at Verbose level.
Options	Create if not exists	See the description in the body of the activity.
	Keep excel file open	If the file is not open at the time the project runs, keeps the workbook open after the project finished running instead of closing it.
	Read Formatting	See the description in the body of the activity.
	Read-only	If selected, the specified workbook is opened in Read-Only mode.
	Save changes	See the description in the body of the activity.

Example of the use of an activity added in modern

Filter Pivot Table

Filter Pivot Table setting items

Setup Location	Setting items	Configuration details
In the Body of the Activity	Source	Click Plus on the right side of the field, select the file, and then select a pivot table to filter, or select Indicate in Excel to open the file and select a pivot table directly from the file.
	Column name	The column to filter on. You can select a filter, column label, or row label.
	Configure Filter	Click this button to configure the filter for the values in the column.
	Clear any existing filter	Select this option if you want to clear existing filters instead of creating a new one.
Common	DisplayName	The name displayed for the activity in the Designer panel.
Input	Column name	The column to filter on. You can select a filter, column label, or row label.
	Configure Filter	Click this button to configure the filter for the values in the column.
Misc	Private	If selected, the values of variables and arguments are no longer logged at Verbose level.
Options	Clear any existing filter	See Clear any existing filter in the body of the activity.

sample process

Change the filters in the pipot table.

– workflow

– Excel of data

– Excel sheet for configuration changes

– Pipot sheet after execution

Autofit Range

Autofit Range setting items

Setup Location	Setting items	Configuration details
In the Body of the Activity	Select a source	Click Plus on the right side of the field, select the file, and then select a named range, table, or sheet as the source, or select Indicate in Excel to open the file and select a range directly from the file.
	Columns	If selected, autofit is applied to the columns in the range.
	Rows	If selected, autofit is applied to the rows in the range.
Common	DisplayName	The name displayed for the activity in the Designer panel.
Input	Range	See Select a source in the body of the activity.
Misc	Private	If selected, the values of variables and arguments are no longer logged at Verbose level.
Options	Columns	If selected, autofit is applied to the rows in the range.
	Rows	If selected, autofit is applied to the rows in the range.

sample process

Change the auto-adjustment of Excel cell ranges.

– workflow

– Target Excel

– Excel after execution

Clear Sheet/Range/Table

Clear Sheet/Range/Table setting item

Setup Location	Setting items	Configuration details
In the Body of the Activity	Range to clear	Click Plus on the right side of the field, then, from the menu, select the file and then a sheet, table, or range to clear, or select Indicate in Excel to indicate a range directly from the file.
	Has headers	If selected, it indicates that the first row in the defined range is a header row and should not be cleared of data.
Common	DisplayName	The name displayed for the activity in the Designer panel.
Input	Range to clear	See Range to clear in the body of the activity.
Misc	Private	If selected, the values of variables and arguments are no longer logged at Verbose level.
Options	Has headers	See Has headers in the body of the activity.

sample process

Clear a specific sheet in Excel.

– workflow

– Target Excel

– Excel after execution

Delete Sheet

Delete Sheet setting item

Setup Location	Setting items	Configuration details
In the Body of the Activity	Select sheet	Click Plus on the right side of the field and then, from the menu, select the file and then the sheet to delete. If you select Indicate in Excel, you can select any cell or range from the sheet you want to delete directly from the file.
Common	DisplayName	The name displayed for the activity in the Designer panel.
Input	Select sheet	See Select sheet in the body of the activity.
Misc	Private	If selected, the values of variables and arguments are no longer logged at Verbose level.

sample process

Delete an Excel sheet.

– workflow

– Target Excel

– Excel after execution

For Each Excel Row

For Each Excel Row setting item

Setup Location	Setting items	Configuration details
In the Body of the Activity	For each	Enter the name by which to refer to the current row in the iteration.
	In range	Click Plus on the right side of the field and then, from the menu, select the range, table, or sheet to use, or select Indicate in Excel to open the file and select a range directly from the file.
	Has headers	If selected, it indicates that the first row in the range is a header row.
	Save after each row	If selected, the Excel file is saved during project execution after each row in the iteration is processed.
Common	DisplayName	The name displayed for the activity in the Designer panel.
Misc	Private	If selected, the values of variables and arguments are no longer logged at Verbose level.
Options	Empty Row Behavior	Select how the automation should behave if an empty row is encountered:
	Has Headers	If selected, it indicates that the first row in the range is a header row.
	In range	Click Plus on the right side of the field and then, from the menu, select the range, table, or sheet to use, or select Indicate in Excel to open the file and select a range directly from the file.
	Save each row	See Save each row in the body of the activity.

sample process

Output data to log using Excel iterations (each row in Excel).

– workflow

– Excel to be read

– Post-execution log

For Each Excel Sheet

For Each Excel Sheet setting item

Setup Location	Setting items	Configuration details
In the Body of the Activity	For each	Enter the name by which to refer to the current sheet in the iteration.
	In workbook	Click Plus on the right side of the field and then, from the menu, select the Excel file whose sheets to iterate through Alternatively, you can select Open in Advanced Editor to enter a VB expression.
Common	DisplayName	The name displayed for the activity in the Designer panel.
Misc	Private	If selected, the values of variables and arguments are no longer logged at Verbose level.
	Workbook	See In workbook in the body of the activity.

sample process

Output cell A1 of each Excel sheet to the log.

– workflow

・”Sheet1″ in the target Excel

– “Sheet2” in the target Excel

– Execution Result Log

Format Cells

Format Cells setting item

Setup Location所	Setting items	Configuration details
In the Body of the Activity	Source	Click Plus on the right side of the field, then, from the menu, select the file and then a sheet, table, or range to format, or select Indicate in Excel to indicate a range directly from the file.
	Format data as type	Click Set Format to open the Format cells window and then select what to format in the cells using the options at the top: Data Type (number format), Alignment (horizontal, vertical, wrap text), Font (font, font style, underline style, font size, color), or Fill (color). Depending on your selection, other options may appear for additional configuration.
Common	DisplayName	The name displayed for the activity in the Designer panel.
Input	See Source	Click Plus on the right side of the field, then, from the menu, select the file and then a sheet, table, or range to format, or select Indicate in Excel to indicate a range directly from the file.
Misc	Private	If selected, the values of variables and arguments are no longer logged at Verbose level.

sample process

Change the formatting of Excel cells.

– workflow

– Formatting of “Format Cells”

– Target Excel

– Excel after execution

Insert Chart

Insert Chart setting item

Setup Location	Setting items	Configuration details
In the Body of the Activity	Chart category	Click the drop-down menu to select the category of the chart to be created.
	Chart type	Click the drop-down menu to select the chart type to be created based on the category that was selected.
	Data Range	Click Plus on the right side of the field, then, from the menu, select the file and then a sheet, table, or range where to insert the chart, or select Indicate in Excel to indicate a range directly from the file.
	Inert into sheet	Click Plus on the right side of the field, then, from the menu, select the file and then a sheet, or select Indicate in Excel to indicate a sheet directly from the file. Alternatively, you can select Custom Input to enter a sheet name manually, or Open in Advanced Editor to enter a VB expression.
	Save chart to	Saves the chart for use in other activities.
Common	DisplayName	The name displayed for the activity in the Designer panel
Input	Chart category	See Chart category in the body of the activity.
	Chart height	Specify the height of the chart.
	Chart left	Coordinates of how far to the right of the sheet the new chart will be inserted.
	Chart top	Coordinates of how far from the top of the sheet the chart will be inserted.
	Chart type	See Chart type in the body of the activity.
	Chart width	Specify the width of the chart.
	Data range	See Data range in the body of the activity.
	Insert into sheet	See Insert into sheet in the body of the activity.
Misc	Private	If selected, the values of variables and arguments are no longer logged at Verbose level.
Output	Save chart to	See the options in the body of the activity.

sample process

Insert graphs into Excel.

– workflow

– Properties of Insert Graph

– Data to be graphed in Excel

– Pre-execution GRAPH SHEET

– Post-execution GRAPH SHEET

Save Excel File As PDF

Save Excel File As PDF setting item

Setup Location	Setting items	Configuration details
In the Body of the Activity	Workbook	Click Plus on the right side of the field and then, from the menu, select the Excel workbook to save as PDF.
	Save as file	Click Browse next to the field, and then browse to the folder where to create the file and enter the file name.
Common	DisplayName	The name displayed for the activity in the Designer panel.
File	Destination pdf path	See Save as file in the body of the activity.
Input	End page	Optionally, specify the number of the last page to include in the PDF.
	Save quality	Select the quality at which to save the file, either Standard Quality (optimized for online publishing and printing) or Minimum Quality (optimized for online publishing).
	Start page	Optionally, enter the number of the first page to include in the PDF.
	Workbook	See Workbook in the body of the activity.
Misc	Private	If selected, the values of variables and arguments are no longer logged at Verbose level.

sample process

Save Excel file as PDF.

– workflow

– Excel to PDF

– PDF-edited file after execution

VLOOKUP

VLOOKUPの設定項目

設定場所	設定項目	設定内容
In the Body of the Activity	Value to lookup	Click Plus on the right side of the field, and then, from the menu, select what to look for using one of the available options:
	In range	Click Plus on the right side of the field, and then, from the menu, select the range where to search for the value.
	Column index	Click Plus on the right side of the field and then, from the menu, select Number and enter the column number containing the value to return.
	Exact match	If selected, it indicates you want to return only exact matches.
	Save to	Click Plus on the right side of the field and then, from the menu, select where to save the result:
Common	DisplayName	The name displayed for the activity in the Designer panel.
Input	Value to lookup	Click Plus on the right side of the field, and then, from the menu, select what to look for using one of the available options:
	In range	Click Plus on the right side of the field, and then, from the menu, select the range where to search for the value.
	Exact match	If selected, it indicates you want to return only exact matches.
	Save to	The name displayed for the activity in the Designer panel.
Misc	Private	If selected, the values of variables and arguments are no longer logged at Verbose level.
Output	Output to	See Save to in the body of the activity.

sample process

Enter the matching value in the cell with VLOOKUP.

– workflow

・VLOOKUP化するデータ

– Vlookup sheet after execution

Write DataTable to Excel

Write DataTable to Excel setting item

Setup Location	Setting items	Configuration details
In the Body of the Activity	What to write	Click Plus on the right side of the field, and then use one of the options in the menu to indicate the data to write:
	Destination	Click Plus on the right side of the field and then, from the menu, select the Excel file and then a named range, table, or sheet where to write the data, Indicate in Excel to open the file and select a range directly from the file, or Custom Input to enter the destination range manually.
	Append	If selected, the data is added starting with the first blank row in the destination range.
	Exclude headers	If selected, the header row or the first row in the source range is not written in the destination range. This option is not selected by default.
Common	DisplayName	The name displayed for the activity in the Designer panel.
Input	What to write	Click Plus on the right side of the field, and then use one of the options in the menu to indicate the data to write:
Misc	Private	If selected, the values of variables and arguments are no longer logged at Verbose level.
Options	Ignore epmty source	–
	Append	If selected, the data is added starting with the first blank row in the destination range.
	Exclude headers	If selected, the header row or the first row in the source range is not written in the destination range.
Output	Destination	Click Plus on the right side of the field and then, from the menu, select the Excel file and then a named range, table, or sheet where to write the data, Indicate in Excel to open the file and select a range directly from the file, or Custom Input to enter the destination range manually.

sample process

Write data tables to Excel.

– workflow

– Data from “Build Data Table”

– Excel file after execution

Summary

Back to Table of Contents