Excel(Power Automate Desktop Action)

05/11/2024

Japanese version.

Power Automate Desktop is an automation tool that can be used to automate various tasks, including those involving Excel files. With Power Automate Desktop, you can automatically perform tasks such as reading, updating, creating, and deleting Excel files.

For example, using Power Automate Desktop, you can read data from an Excel file and insert that data into another database. If you need to add new rows to an Excel file, you can use Power Automate Desktop to enter the necessary information and save the file.

Furthermore, you can use Power Automate Desktop to automatically update information stored in an Excel file. For instance, if you need to regularly update customer information stored in an Excel file, you can use Power Automate Desktop to automatically update the information and save the file.

In summary, Power Automate Desktop is a powerful tool for automating Excel files and can be used to automate many business processes that use Excel files.

This function is related to Excel operation.
It launches the installed Excel, reads and writes data, etc.

Basics

Start and end of Excel operations

First, specify the Excel to be operated on by either Launch Excel or Attach to running Excel.
The resulting Excel instance variable will point to the Excel to be operated on, which is then specified in the parameters of the various actions.
Finally, when the operation is finished, close Close Excel to end the operation.

Worksheet Operations

Most actions target the active (selected) sheet.

If you are using a file with multiple sheets, Specify first in the Set active Excel worksheet section.

Table Operations

Frequently we manipulate tabular data in Excel.
In doing so, we primarily use the following actions

The actions Get first free column/row from Excel worksheet and Get first free row on column from worksheet are useful in this case to determine the operation position.

Copy and Paste

Copy cells from Excel worksheet and Paste cells to Excel worksheet are a set of functions.

Actions

NameImportanceDescription
Launch ExcelHighOpen or create a new Excel file to be manipulated.
To operate Excel with Power Automate Desktop, this Action or Attach to running Excel Action must be performed first.
Attach to running ExcelMiddleSpecify an already opened Excel file as the target of the operation. To perform an Excel operation with Power Automate Desktop, either this action or Launch Excel Action must be performed first, although Launch Excel Action is more common in practice.
Read from Excel worksheetHighExtract values from an Excel book by specifying a cell or range of cells.
Get active cell on Excel worksheetMiddleGet the currently selected cell position (number of rows and columns).
Save ExcelMiddleSave the edited Excel file without closing it.
Write to Excel worksheetHighSet values in an Excel file by specifying a cell or range of cells.
Close ExcelHighCloses an Excel file that has been manipulated by Power Automate Desktop. You can also save the file.
Set active Excel worksheetHighChange the active (selected) sheet by specifying the sheet name or index. This is a frequently used action because many actions target the active sheet.
Add new worksheetMiddleAdd a new worksheet by specifying the worksheet name and location.
Get first free column/row from Excel worksheetHighRetrieves rows or columns with no data. This action is often used before an action that manipulates data to determine the position to be manipulated.
Get column name on Excel worksheetHighObtains the column name (A to XFD) of the specified column. (Convert numeric column numbers to text columns)
Sort cells in Excel worksheetHighThis action sorts the specified range or table.
This action was added in the May 2024 update.
Filter cells in Excel worksheetHighThis action applies a filter to a specified range or table.
This action was added in the May 2024 update.
Clear filters in Excel worksheetHighThis action clears the filter on the specified sheet or table.
This action was added in the May 2024 update.
Clear cells in Excel worksheetHighThis action clears the specified cell or range of cells.
The formatting as well as the values will be cleared.
This action was added in the March 2024 update.
Get empty cellHighThis action retrieves the position of an empty cell from a specified cell range.
This action was added in the March 2024 update.

Advanced

NameImportanceDescription
Resize columns/rows in Excel worksheetMiddleWith actions to change column widths and row heights,
The size can be specified numerically or automatically (same as double-clicking).
Run Excel macroHighRun the macro by specifying the macro name and parameters.
This is not possible with Attach to running Excel.
Get Active Excel worksheetLowGets the name of the active (currently selected) worksheet.
Get all Excel worksheetsMiddleGet a list of worksheet names present in the Excel file in list format.
Delete Excel worksheetMiddleIncludes how to use the action to delete a worksheet by name or index.
Rename Excel worksheetMiddleIncludes how to use the action to delete a worksheet by name or index.
Activate cell in Excel worksheetLowMakes a single cell active (selected). Specify the column/row or the distance from the current position.
This is a simpler way to specify the cell position in each action than to use this, so it is not necessary to specify the cell position in each action.
For this reason, it will often be used for macros and other circumstances outside of Power Automate Desktop.
Select cells in Excel worksheetLowMakes cell range active (selected). Specify the column/row or the distance from the current position.
This is a simpler way to specify the cell range position in each action than to use this, so it is not necessary to specify the cell position in each action.
For this reason, it will often be used for macros and other circumstances outside of Power Automate Desktop.
Get selected cell range from Excel worksheetLowObtains the number of first columns/rows and last columns/rows in the active (selected) cell range.
Copy cells from Excel worksheetMiddleSpecify a cell or range of cells to copy. The copied content is pasted using Paste cells to Excel worksheet.
Paste cells to Excel worksheetMiddleSpecify the cell location and paste the contents copied in Copy cells from Excel worksheet.
Delete from Excel worksheetHighDelete a cell or range of cells.
Insert row to Excel worksheetHighAdd an empty row above the specified row.
Delete row from Excel worksheetHighDelete one row from the Excel worksheet.
Insert column to Excel worksheetMiddleAdd an empty row column the specified column.
Delete column from Excel worksheetMiddleDeletes a column by specifying the number of columns.
Find and replace cells in Excel worksheetHighFind or Find and Replace cells.
Search results are obtained by row and column position (numerical value).
Get first free row on column from worksheetMiddleRetrieves rows with no data. This action is often used before an action that manipulates data to determine the position of the data to be manipulated. It is useful when there are multiple tables on a sheet.
Since it is safer to have only one table per sheet to operate on
It is recommended to avoid using this action if at all possible.