Previous

Next

Exercise - Calculate modulo with VBScript

Completed

In this exercise, you'll create a flow that calculates how many overtime hours employees have worked. Consider that a typical working day is eight hours.

Note

Before creating the flow, download the Employees.xlsx file that is required for this exercise. Select Download on the right side of the page and extract the downloaded file to your local computer.

1. Launch the Power Automate for desktop console and click on the + New flow.

   

2. Name the new flow as Overtime calculator and click Create.

   

3. Under Actions search for launch.

   

4. Add the Launch Excel action to the workspace and configure it to launch the Employees.xlsx file.

   

5. Under Actions search for get first and double-click on Get first free column/row from Excel worksheet.

   

6. Use the Get first free column/row from Excel worksheet action to find the first free row in the file.

   

7. Under Actions search for loop and double-click on it.

   

8. In Start from, manually type in 1. In End to, click on {X}, then select FirstFreeRow.

   

9. Update the End to formula as shown below. Finally, in Increment by, add 1.

   

10. Under Actions search for read from excel. You can either double-click on Read from Excel worksheet or drag-and-drop it between Loop and End.

    The action is used to read the cell that contains the current employee's total working hours.

    Note

    In Variables produced, double-click on ExcelData and change the text to TotalHours.

    

11. Under Actions search for run vbs and drag-and-drop Run VBScript below Read from Excel worksheet

12. In VBScript to run type result=, then select the {X} and select TotalHours and then Save.

    

13. Complete the rest of the script as shown below.

    

14. This is a good time to save the flow you have built so far.

    

15. The return value of the Run VBScript action is a text value. Use a Convert text to number action to convert it to a number.

    

16. Use a Write to Excel worksheet action to write the numerical value in the current row's third cell.

    

17. Outside the loop, add a Close Excel action to save and close the file.

18. Save the flow and then run it to test that every action works as expected.

    

Continue