Excel(Power Automate Desktop Action)
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
- Read from Excel worksheet
- Write to Excel worksheet
- Sort cells in Excel worksheet
- Filter cells in Excel worksheet
- Clear filters in Excel worksheet
- Get empty cell
- Clear cells in Excel worksheet
- Insert row to Excel worksheet
- Delete row from Excel worksheet
- Insert column to Excel worksheet
- Delete row from Excel worksheet
- Set color of cells in Excel worksheet
- Copy Excel worksheet
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
| Name | Importance | Description |
|---|---|---|
| Launch Excel | High | Open 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 Excel | Middle | Specify 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 worksheet | High | Extract values from an Excel book by specifying a cell or range of cells. To get formula, use “Read formula from Excel”. |
| Get active cell on Excel worksheet | Middle | Get the currently selected cell position (number of rows and columns). |
| Save Excel | Middle | Save the edited Excel file without closing it. |
| Write to Excel worksheet | High | Set values in an Excel file by specifying a cell or range of cells. |
| Close Excel | High | Closes an Excel file that has been manipulated by Power Automate Desktop. You can also save the file. |
| Set active Excel worksheet | High | Change 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 worksheet | Middle | Add a new worksheet by specifying the worksheet name and location. |
| Get first free column/row from Excel worksheet | High | Retrieves 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 worksheet | High | Obtains the column name (A to XFD) of the specified column. (Convert numeric column numbers to text columns) |
| Sort cells in Excel worksheet | High | This action sorts the specified range or table. This action was added in the May 2024 update. |
| Filter cells in Excel worksheet | High | This action applies a filter to a specified range or table. This action was added in the May 2024 update. |
| Clear filters in Excel worksheet | High | This action clears the filter on the specified sheet or table. This action was added in the May 2024 update. |
| Clear cells in Excel worksheet | High | This 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 cell | High | This action retrieves the position of an empty cell from a specified cell range. This action was added in the March 2024 update. |
Advanced
| Name | Importance | Description |
|---|---|---|
| Resize columns/rows in Excel worksheet | Middle | With actions to change column widths and row heights, The size can be specified numerically or automatically (same as double-clicking). |
| Run Excel macro | High | Run the macro by specifying the macro name and parameters. This is not possible with Attach to running Excel. |
| Get Active Excel worksheet | Low | Gets the name of the active (currently selected) worksheet. |
| Get all Excel worksheets | Middle | Get a list of worksheet names present in the Excel file in list format. |
| Delete Excel worksheet | Middle | Includes how to use the action to delete a worksheet by name or index. |
| Rename Excel worksheet | Middle | Includes how to use the action to delete a worksheet by name or index. |
| Copy Excel worksheet | Middle | This Action is that duplicates an existing worksheet. |
| Activate cell in Excel worksheet | Low | Makes 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 worksheet | Low | Makes 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 worksheet | Low | Obtains the number of first columns/rows and last columns/rows in the active (selected) cell range. |
| Copy cells from Excel worksheet | Middle | Specify a cell or range of cells to copy. Paste the copied content using either ‘Paste cells to Excel worksheet’ or ‘Append cells in Excel worksheet’. |
| Paste cells to Excel worksheet | Middle | Specify the cell location and paste the contents copied in Copy cells from Excel worksheet. This is similar to ‘Append cells in Excel worksheet, but the method for specifying the paste destination is different. ‘Paste cells to Excel worksheet’ specifies the position by cell address, but ’Append cells in Excel worksheet’ specifies the table and pastes it to the end of the table. |
| Delete from Excel worksheet | High | Delete a cell or range of cells. |
| Insert row to Excel worksheet | High | Add an empty row above the specified row. |
| Delete row from Excel worksheet | High | Delete one row from the Excel worksheet. |
| Insert column to Excel worksheet | Middle | Add an empty row column the specified column. |
| Delete column from Excel worksheet | Middle | Deletes a column by specifying the number of columns. |
| Find and replace cells in Excel worksheet | High | Find or Find and Replace cells. Search results are obtained by row and column position (numerical value). |
| Get first free row on column from worksheet | Middle | Retrieves 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. |
| Read formula from Excel | Middle | This action sets the formula of the specified cell to a variable. You cannot read the value with this action. To get values, use “Read from Excel worksheet”. |
| Get table range from Excel worksheet | Low | This is an action that gets the position of a table or pivot table. |
| Auto fill cells in Excel worksheet | Low | This is the auto-fill action. |
| Append cells in Excel worksheet | Middle | This is an action for pasting copied cells. It is similar to ‘Paste cells to Excel worksheet’, but the method for specifying the position differs. While ‘Paste cells to Excel worksheet’ specifies the position by cell address, ‘Append cells in Excel worksheet’ specifies the table and pastes at the end of the table. |
| Set color of cells in Excel worksheet | High | This is an action that changes the color of the cells in the specified cell range. This action was added in the August 2024 update. |