Excel Automation with Power Automate Desktop

 

Excel Automation with Power Automate Desktop: How to Read and Write Data

Microsoft Excel is a powerful tool for data management, but manually handling data across multiple Excel files can be time-consuming and prone to errors. Thankfully, with Power Automate Desktop (PAD), you can automate these repetitive tasks. In this blog post, we’ll walk through how to read data from Excel and write data to Excel using Power Automate Desktop.

Whether you're updating reports, processing data, or extracting information, Excel automation in PAD can help save time and increase productivity.

Why Use Power Automate Desktop for Excel Automation?

Power Automate Desktop allows you to automate tasks on your Windows computer, such as:

• Opening and closing Excel files
• Reading data from cells
• Writing data to cells
• Creating reports
• Extracting and transforming data

With just a few clicks, you can eliminate manual work and let your automation flow handle the repetitive tasks!

Prerequisites

Before we dive into the examples, here’s what you need:

• Power Automate Desktop installed on your PC.
• An Excel file you want to work with (either create one or use an existing file).
• Basic understanding of Power Automate Desktop interface.

Let’s get started with a simple example!

Example Scenario

Imagine you have an Excel file called EmployeeData.xlsx with the following columns: Name, Age, Department.

We want to:

1. Read the data from this file.
2. Write new employee data into the file.

Steps to Automate Excel with Power Automate Desktop

Step 1: Launch Power Automate Desktop and Create a New Flow

1. Open Power Automate Desktop.
2. Create a new flow (File → New → Flow) and give it a name like "Excel Automation Example."

Step 2: Launch Excel

To interact with Excel, we first need to launch Excel within the flow.

1. In the action pane (on the left), type "Launch Excel."
2. Drag and drop the Launch Excel action into the main flow window.

Configure the Launch Excel action:

• Set Visible to True (so you can see Excel when it opens).
• Set Instance to a variable (e.g., ExcelInstance). This stores the reference to the Excel application, so you can use it in later actions.

This will open Excel and keep it running in the background for the rest of the automation.

Step 3: Read Data from Excel

Now, let’s read the data from the Excel file.

1. In the action pane, search for Read from Excel worksheet and drag it to the flow.
2. Configure the action:
   • Excel instance: Set it to the ExcelInstance variable (which you created earlier).
   • Workbook path: Browse and select the Excel file you want to read from (e.g., C:\Users\YourName\Documents\EmployeeData.xlsx).
   • Start from: Enter the range of cells to read from (e.g., A1:C10 if your data is in cells A1 to C10).
   • Output to: Create a new variable (e.g., EmployeeData) to store the data read from Excel.

This action will extract data from the specified range and save it as a list of lists in the EmployeeData variable, where each row of data will be a list.

Step 4: Process the Data (Optional)

If you want to process the data (e.g., filter, transform, or log it), you can use a For Each loop.

1. Drag the For Each action onto the flow.
2. Set the Value to iterate to EmployeeData. This will loop through each row of data in your Excel sheet.
3. Inside the loop, you can display each employee's name, age, and department using a Display message action.

Example:

• EmployeeData[CurrentItem][0] → Name
• EmployeeData[CurrentItem][1] → Age
• EmployeeData[CurrentItem][2] → Department

This will show a message for each employee in the data.

Step 5: Write Data to Excel

Now let’s write new data into the Excel file.

1. Search for Write to Excel worksheet and add it to the flow.
2. Configure the action:
   • Excel instance: Set it to ExcelInstance.
   • Workbook path: Choose the same file where you want to add data (e.g., C:\Users\YourName\Documents\EmployeeData.xlsx).
   • Data to write: Create a new list with the data you want to add. For example, NewEmployeeData = [["John Doe", 28, "Sales"], ["Jane Smith", 34, "Marketing"]].
   • Start from: Set the cell where you want to start writing (e.g., A11).

This will write the new employee data starting at cell A11 in the sheet.

Step 6: Save and Close the Excel File

Once the data is written, you’ll want to save and close the Excel file.

1. Add the Save Excel action to save the file (set Save changes to True).
2. Finally, add the Close Excel action to close the Excel application (set Save changes to True).

Complete Flow Example

Here’s how your complete flow should look in Power Automate Desktop:

1. Launch Excel (Visible = True, Instance = ExcelInstance).
2. Read from Excel worksheet (Excel instance = ExcelInstance, Workbook path = "C:\Users\YourName\Documents\EmployeeData.xlsx", Start from = A1:C10, Output to = EmployeeData).
3. For Each (Value to iterate = EmployeeData):
   • Display message (Show data for each employee).
4. Write to Excel worksheet (Excel instance = ExcelInstance, Workbook path = "C:\Users\YourName\Documents\EmployeeData.xlsx", Data to write = NewEmployeeData, Start from = A11).
5. Save Excel (Save changes = True).
6. Close Excel (Save changes = True).

Tips for Excel Automation in Power Automate Desktop

• Handle Large Data: If you're working with large Excel files, try to reduce the range you’re reading/writing to avoid performance issues.
• Excel Visibility: If you don't need to see Excel open, you can set the Visible option to False when launching Excel.
• Error Handling: Always consider adding error handling (e.g., try-catch) to ensure your automation doesn't break if something goes wrong.
• Formatting: You can format the data (e.g., numbers, dates) directly in Excel before reading or after writing it to maintain consistency.

Conclusion

Excel automation using Power Automate Desktop is a game-changer for reducing manual work. By using simple actions like Launch Excel, Read from Excel, and Write to Excel, you can automate common Excel tasks like extracting data, processing information, and updating records. This helps improve productivity, reduce errors, and free up time for more important tasks.

Now that you’ve seen how to automate reading and writing data to Excel in Power Automate Desktop, it’s your turn! Give it a try and start building your own Excel automation workflows.

Happy automating!