Power Automate Desktop: Data Entry From Excel To Website

Power Automate Desktop is a free application from Microsoft you can download to automate personal or business-related tasks on any Windows computer. One of the most popular use cases for Power Automate Desktop is taking data from an Excel and file and entering it into a form on website. Its absolutely amazing to watch as your computer quickly completes the boring task that used to take you hours. In this step-by-step tutorial I will show you how automate data entry from Excel to a form on a website.

Table Of Contents:
•  Introduction: The Insurance Claims Website
•  Create An Insurance Claim Form With Microsoft Forms
•  Make An Excel Spreadsheet With Insurance Claims Data
•  Test Desktop Flow Actions To Read The Excel Worksheet
•  Launch A New Web Browser Instance
•  Use The Web Recorder To Capture Data Entry Actions
•  Insert A For Each Loop To Repeat Data Entry Actions
•  Close The Web Browser Window
•  Run The Power Automate Desktop Flow To Perform Data Entry

Introduction: The Insurance Claims Website

A construction company has a list of employee insurance claims in an Excel spreadsheet. Using Power Automate Desktop the company automatically enters each claim into a form on their insurance company’s website.

Create An Insurance Claim Form With Microsoft Forms

To do this example we need to build an insurance claim form to open in our web browser. We can use Microsoft Forms – a free form builder that comes with your Office 365 license for this purpose. Open http://forms.office.com/ and create a new form.

If you don’t have access to Microsoft forms just use the insurance claim form I created here.

The form should these 4 fields with the following data types:

• 1. Employee Name (text)
• 2 Claim Amount (text)
• 3. Claim Date (date)
• 4. Claim Type (text)
  
  

Once we are finished setting up the web form we click on the share button to obtain the form’s web address. We will use the web address to tell our Power Automate Desktop flow where the Insurance Claim Form can be found.

Make An Excel Spreadsheet With Insurance Claims Data

We also require an Excel spreadsheet with insurance claims data to enter into the website’s form. Open Excel and input the following data. When saving the spreadsheet make a note of the filename and folder path where it is stored. The path to my spreadsheet is:

C:\RPA\WriteExcelToWebForm\MedicalClaims.xlsx

If you don’t want to type this out manually, you can get the Excel file from my Github repository.

Get The Values From An Excel Worksheet

We have now created the insurance claims data in Excel and and the Insurance Claims web form in Microsoft Forms. It is time to build a Power Automate Desktop flow to perform data entry from Excel to a website. Open Power Automate Desktop and create a new flow.

Add the 1st flow action Set Variable. Input the folder path and file name in the To field.

Launch Excel using the 2nd flow action. Change the Launch Excel parameter to and open the following document then make the document path reference Excel_File_Path variable.

Next we will extract data from the opened Excel file. Insert a Read from Excel worksheet action, point it to the previous step’s Excel_Instance and retrieve all available values form the worksheet. Expand the Advanced options and set first line of range contains column names to true.

Then close the Excel workbook and do not save it.

The first 4 steps in our Power Automate flow should look like this.

Test Desktop Flow Actions To Read The Excel Worksheet

Before we go any further let’s make sure our Power Automate Desktop flow will successfully retrieve the Excel data and store it in a variable. Click the Play button and wait for the flow to run. The ExcelData variable should show 10 Rows, 4 columns once it is finished. Double click on the variable to inspect it.

We can see the data from the Excel worksheet has been saved to the ExcelData varaible.

Launch A New Web Browser Instance

After the insurance claims data is extracted from the Excel spreadsheet we must open a web browser to the insurance claims form. Insert a new Launch new Chrome (or other web browser) step. Choose new instance as the launch mode and supply the initial URL for Microsoft Form we built earlier.

Click the play button to run the flow to verify if the web form opens as expected.

The insurance claims web form should appear a few moments after the flow starts running. Do not close the browser tab with the form. We will require it to be open for the next step.

Use The Web Recorder To Capture Data Entry Actions

Creating the actions to do data entry from Excel to a website is made easy by Power Automate Desktop’s web recorder. When we turn on the web recorder it creates action for each task we performed on website (populating a text field, clicking on a link, etc.). Then when we are done recording those actions become part of our flow.

Click on the web recorder to start icon to start recording.

Select Google Chrome as the web browser, choose the web browser instance called Browser and pick the Insurance Claim Form tab. If you do not see Insurance Claim Form as a tab it is because you closed the browser tab with our Microsoft Form.

When the Web Recorder menu appears, click the Record button.

Now go ahead and fill in each field on the insurance claim form with fake values, submit the form, then click the link to start another response. As we do this we can see the actions generated in the web recorder. To finish the recording click the Pause button, then select Finish.

Any actions captured by the web recorder are added to the end of our main flow.

Insert A For Each Loop To Repeat Data Entry Actions

We only submitted one form with the web recorder but instead we want to submit many forms – one for each row in the Excel file. To accomplish this place a For Each loop around the steps autogenerated using the web recorder. Also, delete the flow action called Go To Webpage since we don’t want to open the form several times.

Edit the For Each loop to iterate over the ExcelData variable.

Edit Data Entry Actions To Input Values From Excel File

Right now the data entry actions for populating a text field on a web page are using hardcoded values we manually typed in. We want them to use values from the Excel file. To do this we must edit steps 7-10 of our Power Automate Desktop flow.

Change the 1st populate text field on web page action’s text parameter to CurrentItem[‘Full Name’].

Then update the 2nd action’s text parameter to CurrentItem[‘Claim Amount’].

Before continuing to the 3rd action we must convert the datetime value in the ExcelData variable’s Claim Date field to a text value. Add a new Convert datetime to text action to the flow and choose the standard format Short date.

Use the FormattedDateTime variable in the 3rd action’s text property.

Finally, complete the 4th action’s Text field with CurrentItem[‘Claim Type’]

The result of all our updates should make the flow look like this.

Close The Web Browser Window

We’re almost done. The last action we must do is to close the web browser once the loop has completed.

Add the Close web browser action to the end of the flow. The completed desktop flow should look like this.

Run The Power Automate Desktop Flow To Perform Data Entry

Press the play button to run the desktop flow. It will read-in data from the Excel file, open the the insurance claim form in Google Chrome, submit a form for each row in the table and then close the web browser.

Questions?

If you have any questions about Power Automate Desktop: Data Entry From Excel To Website please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.