Intro

Excel is the most used business application across all companies. As a result is also the application we automate the most in Microsoft Power Automate Desktop.

Learning Goals

• Primarily
  • Automating Excel
• Secondarily
  • Intro to VBA and VBScript in Power Automate Desktop

Description

Create a flow that

• Creates a ‘Profit’ column to the left of ‘Units Sold’
• Creates a ‘Total Profit’ column to the right of ‘Units Sold’
• Makes the headers bold
• Iterates through the data and update
  • The Profit with [Price – Cost]
  • The Total Profit with [(Price – Cost) * Units Sold]
• Colors Total Profit cells on the following scheme
  • < 70: Red
  • >= 70 AND < 100: Yellow
  • >= 100: Green
• Resizes the columns to autofit the data

Course Materials

Download the course materials: Candy.xlsx

VBA Code for the Lesson

Sub FormatData()
    Const ProfitColumn As Integer = 6 ' Column F is the Profit column
    Dim ProfitRange As Range
    Set ProfitRange = Range("F2:F" & Range("A" & Rows.Count).End(xlUp).Row)

    ' Make headers bold
    Range("A1:F1").Font.Bold = True

    ' Color cells in Profit column based on profit value
    Dim Cell As Range
    For Each Cell In ProfitRange
        Select Case Cell.Value
            Case Is < 70
                Cell.Interior.ColorIndex = 3 ' Red
            Case Is < 100
                Cell.Interior.ColorIndex = 6 ' Yellow
            Case Else
                Cell.Interior.ColorIndex = 4 ' Green
        End Select
    Next Cell
End Sub

VBScript for the Lesson

'Create an instance of the Excel application object
Set objExcel = CreateObject("Excel.Application")

'Open the Excel workbook
Set objWorkbook = objExcel.Workbooks.Open("c:\YourExcelPath.xlsx")

'Get a reference to the first worksheet
Set objWorksheet = objWorkbook.Sheets("Sheet1")

' Loop through each sheet in the Excel book
'For Each objWorksheet In objWorkbook.Worksheets
  objWorksheet.Activate

'Get the used range of the worksheet
Set objRange = objWorksheet.UsedRange

'Make the headers bold
objRange.Rows(1).Font.Bold = True

'Get the total number of rows in the used range
intRows = objRange.Rows.Count

'Loop through the rows in the worksheet
For i = 2 to intRows
    'Get a reference to the current row
    Set objRow = objRange.Rows(i)

    'Color Cells based on conditions
    If objRow.Cells(1,6).Value < 70 Then
    	objRow.Cells(1,6).Interior.ColorIndex = 3
    ElseIf objRow.Cells(1,6).Value < 100 Then
    	objRow.Cells(1,6).Interior.ColorIndex = 6
    ElseIf objRow.Cells(1,6).Value >= 100 Then
    	objRow.Cells(1,6).Interior.ColorIndex = 4
    End If

    '...

Next

'Save the workbook
objWorkbook.Save

'Close the workbook
objWorkbook.Close

'Quit the Excel application
objExcel.Quit

Video