Write to Excel worksheet Power Automate Desktop

Intro

Sespi Cola want to automate how their salespersons are performing.

What you will learn

• Primarily:
  • Excel automation
• Secondarily:
  • Using Excel macros/VBA
  • Working with variables

Client Name

Sespi Cola

Process Name

ExcelEmployees

Description

Sespi Cola wants your help to do Excel Automation for their employee data:

• Download the Excel book here
• Yearly Sales
  • Calculate the Yearly Sales (Q1Q2+Q3Q4) and write the result for each row in the Yearly Sales column
• Monthly Sales
  • Calculate the monthly sales for each of the employees in the Excel sheet (Employees) and write it to the Monthly Sales column
• Color the Monthly Sales cells based on the value of them:
  • If Monthly Sales < 10,000: Red
  • If Monthly Sales >= 10,000 and Monthly Sales <= 30,000: Yellow
  • If Monthly Sales > 30,000: Green

Hints

• Set variable, store you Excel Path here
• Launch Excel
• Close Excel in the end
• Set Active Excel Worksheet
• Get first free column/row from Excel worksheet
• Read from Excel worksheet
• Yearly Sales
  • For each
  • Convert text to number
    • Text to convert: %Employee[‘Q1Q2’]%
    • Variables produced: Q1Q2
  • Convert text to number
    • Text to convert: %Employee[‘Q3Q4’]%
    • Variables produced: Q3Q4
  • Create an index variable to get the right row
  • Write to Excel worksheet
    • %Q1Q2 + Q3Q4%
• Monthly Sales
  • Set variable
    • Variable: Monthlysales, Value: %(Q1Q2 + Q3Q4) / 12%
  • Write to Excel worksheet
    • %(Q1Q2 + Q3Q4) / 12%
•  Color
  • We need to enable macros for our Excel Book
    • Save it as a .xlsm file
    • If your Developer menu tab in Excel is not enabled:
      • File – Options – Customize Ribbon and make sure Developer is enabled.
    • Developer – Visual Basic
      • Right Click and Insert/Module
    • Paste in the VBA code below
      • MonthlySales and RowNumber are (ingoing) arguments
  • Run Excel macro
    • ColorCode;%MonthlySales%;%RowNumber%

Sub ColorCode(MonthlySales As Double, RowNumber As Integer) 'Paint red if it's a bad performing employee If MonthlySales < 10000 Then Range("F" & RowNumber).Interior.ColorIndex = 3 'Paint yellow if it's an average performing employee ElseIf MonthlySales >= 10000 And MonthlySales <= 30000 Then Range("F" & RowNumber).Interior.ColorIndex = 6 'Paint green if it's a good performing employee ElseIf MonthlySales > 30000 Then Range("F" & RowNumber).Interior.ColorIndex = 4 End If End Sub

Video Guide

I started a new role in October with our RPA (Robotic Process Automation) team, which involves working with Microsoft Power Automate and Power Automate Desktop (and still Alteryx). I’ve been learning the ropes the last few months and want to show an example of how you can use these tools to automate small but mundane tasks, like extracting a value from email and updating an Excel spreadsheet. This post will explain how to update an Excel file with Power Automate Desktop.

Use Case

First, know that this Power Automate Desktop flow (PAD) is part of a Power Automate (PA) flow. I use Power Automate to trigger the desktop flow. To keep the post a reasonable length, I’m will cover just the PAD portion. In a future post, I’ll showcase the PA piece. Here is my full use case.

1. A user receives an email (approximately once a month) to update a value in a spreadsheet. The email contains a start date, end date, and “shrink” value.
2. PA monitors the inbox and triggers the flow when it recognizes the email by a specific subject line.
3. PA parses the email and extracts the start date, end date, and shrink value.
4. Then, PA triggers the desktop flow shown below to populate the spreadsheet with the values from the email.

Information

If the spreadsheet had been saved in Teams or Sharepoint, I could have performed all automation with PA. Because the file was located on the shared drive, I incorporated PAD into the solution. It might have been possible to still stay in PA, but this was the option I chose.

The Flow Actions

To support this flow, I set up 5 input variables. Don’t worry about the blurry ones. I have since made the flow do a few extra things, but I want to keep this use case simple, so I blurred out the input variables that don’t apply. PA transfers the values from the PA flow (where they were extracted from the email) into the PAD flow via the input variables. PAD updates the Excel file. The excel input variable is the location of the file on our shared drive.

Detailed Steps

Step 1

First, PAD opens the excel file using the file path stored in the excel input variable. This produces a flow variable called ExcelInstance, which is referenced in other actions.

Step 2

To put the data in the right place, PAD sets the active worksheet to a worksheet specified with the input variable worksheet.

Warning

If users change the name of the worksheet, it will break the flow.

Step 3

Next, PAD finds the first free column and row. Free row means the first blank row. This is important because I always want to append to the existing data set. This action produces an output variable called FirstFreeRow that I will reference in future actions.

Steps 4, 5, 6 (Updating start date, end date, and shrink)

Now, I start writing to the Excel file using my input variables (start date, end date, shrink) and the flow variable FirstFreeRow. Also note, the flow variable ExcelInstance gets referenced here. I hard code the column because it will not change and use the FirstFreeRow flow variable to specify the row.

The screenshot shows the start date being updated. The end date and shrink are updated in the exact same way.Steps 7

Finally, I use the Close Excel action to close the instance of Excel and save the document.

Conclusion

And that’s how I updated an Excel spreadsheet with information from an email. I particularly like this solution because it’s dynamic. I am not hard coding both the row and the column. So many of the posts I found on the internet were just hardcoded, which seems useless to me.

In conclusion, now you know how to update an Excel file with Power Automate Desktop. I’ll show the PA piece where I extract the data from the email in a future post.

How do I write data in Excel using Power Automate desktop?

Can Power Automate write to Excel?

How do I automatically input data from another sheet in Excel?

How do you update rows in Excel using Power Automate?