Excel in Power Automate for 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