Automating Account Data Handling with Power Automate Desktop

Introduction

5 min readJul 18, 2024

Hey there! Do you want to make your computer do boring tasks like entering account data from Excel to an invoicing application? With Power Automate Desktop, you can! This guide will show you how to create a flow that reads data from an Excel sheet and enters it into an application. It’s easy, fun, and perfect for beginners!

Story: Making Data Entry Easy!

Imagine you have to enter account data into a system every day. Doing it manually is slow and boring. But don’t worry! You can automate this task with Power Automate Desktop. Here’s how to do it step-by-step.

Code for Copying
Here’s the code block for you to copy into Power Automate Desktop:

1. Run application 'C:\Users\yourname\Desktop\Contoso Invoicing.lnk' and store its process ID into 'AppProcessId'
2. Launch Excel and open document 'C:\Users\yourname\Desktop\Account.xlsx' using an existing Excel process and store it into 'ExcelInstance'
3. Get first free column/row from Excel worksheet and store the result into 'FirstFreeColumn' and 'FirstFreeRow'
4. Read from Excel worksheet and store the data into 'ExcelData'
5. Set variable 'row' to '2'
6. Click UI element in window 'Accounts'
7. For each 'CurrentItem' in 'ExcelData':
   a. Populate text field in window with 'CurrentItem['Account Name']'
   b. Populate text field in window with 'CurrentItem['Primary Contact']'
   c. Populate text field in window with 'CurrentItem['Contact Email']'
   d. Get details of the UI element in window and store the result into 'IDNO'
   e. Write 'IDNO' to Excel worksheet in cell 'E' and row 'row'
   f. Click UI element in window to complete the entry
   g. Increase variable 'row' by '1'

Step-by-Step Instructions

Step 1: Install Contoso Invoicing Application

Before starting with Power Automate Desktop, make sure you have the Contoso Invoicing application installed on your computer. Here’s how to do it:

Download Contoso Invoicing

Go to the official Microsoft Learn page to download the setup file for Contoso Invoicing.

Install Contoso Invoicing

Run the downloaded setup file.
Follow the on-screen instructions to install the application.
Once installed, you should find a shortcut named ‘Contoso Invoicing.lnk’ on your desktop.

Step 2: Run the Invoicing Application

Action: Run application

Details: Open the invoicing application.

In Power Automate Desktop, create a new flow and name it “Account Data Handling Automation”.
Search for “Run application” in the Actions pane.
Drag it to the workspace.
Set the application path to your invoicing app (e.g., ‘C:\Users\yourname\Desktop\Contoso Invoicing.lnk’).
Store the process ID in a variable called ‘AppProcessId’.

Step 3: Launch Excel

Action: Launch Excel

Details: Open the Excel document that contains your account data.

Search for “Launch Excel”.
Drag it to the workspace.
Set the file path to your Excel file (e.g., ‘C:\Users\yourname\Desktop\Account.xlsx’).
Store the instance in a variable called ‘ExcelInstance’.

Step 4: Get First Free Column/Row

Action: Get first free column/row from Excel worksheet

Details: Find the first empty row in the active worksheet.

Search for “Get first free column/row”.
Drag it to the workspace.
Use ‘ExcelInstance’ as the Excel instance.
Store the first free column in ‘FirstFreeColumn’ and the first free row in ‘FirstFreeRow’.

Step 5: Read Data from Excel

Action: Read from Excel worksheet

Details: Read data from the specified range in your Excel sheet.

Search for “Read from Excel worksheet”.
Drag it to the workspace.
Set the range from ‘A1’ to ‘FirstFreeColumn — 1’ and ‘FirstFreeRow — 1’.
Store the data in a variable called ‘ExcelData’.

Step 6: Set Variable

Action: Set variable

Details: Set the initial row value for writing back to Excel.

Search for “Set variable”.
Drag it to the workspace.
Set the variable ‘row’ to ‘2’.

Step 7: Click ‘Accounts’ Button

Action: Click UI element in window

Details: Click on the ‘Accounts’ button in the application.

Search for “Click UI element in window”.
Drag it to the workspace.
Click “Add UI element” and select the ‘Accounts’ button in your invoicing app.

Step 8: Enter Data for Each Account

Action: For each item in ‘ExcelData’

Details: Loop through each row of data in ‘ExcelData’.

Search for “For each”.
Drag it to the workspace.
Set it to loop through ‘ExcelData’ and call each item ‘CurrentItem’.

Action: Populate text fields in the application

Details: Enter the account details from ‘ExcelData’.

Search for “Populate text field in window”.
Drag it inside the loop.
Select the text fields in your invoicing app and populate them with the corresponding data from ‘CurrentItem[‘Account Name’]’, ‘CurrentItem[‘Primary Contact’]’, and ‘CurrentItem[‘Contact Email’]’.

Action: Get details of the UI element in window

Details: Get any additional required information from the invoicing app.

Search for “Get details of the UI element in window”.
Drag it inside the loop.
Select the required UI element and store the result in ‘IDNO’.

Action: Write to Excel worksheet

Details: Write back the processed data to Excel.

Search for “Write to Excel worksheet”.
Drag it inside the loop.
Write the ‘IDNO’ to the corresponding cell in the Excel sheet.
Set the cell to ‘E’ and the row to ‘row’.

Action: Click to complete and save

Details: Click any final button to complete the entry in the invoicing app.

Search for “Click UI element in window”.
Drag it inside the loop.
Select the relevant UI element to complete the entry.

Action: Increase variable

Details: Increment the row index for writing back to Excel.

Search for “Increase variable”.
Drag it inside the loop.
Increment ‘row’ by ‘1’.

Conclusion

And that’s it! By following these steps, you’ve automated entering account data from Excel into an invoicing application using Power Automate Desktop. This saves time and makes the task more fun and efficient. Enjoy automating your tasks!

Note this is a picture of the command. >>>