Excel in Power Automate for Desktop

Intro Sespi Cola want to automate how their salespersons are performing. What you will learn Client Name Sespi Cola Process Name ExcelEmployees Description Sespi Cola wants your help to do Excel Automation for their employee data: Hints Video Guide

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

Please accept marketing-cookies to watch this content.

Write a comment