How to read an Excel file and process it line by line in a loop. Power Automate Desktop.
This section introduces a flow that reads an Excel table and performs some action, one row at a time.
As an example, read the following table.
Overall flow
Robin(for copy and paste)
It can be copied and pasted into Power Automate Desktop.
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Test\\Test.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: $'''Sheet1'''
Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData
Excel.CloseExcel.Close Instance: ExcelInstance
LOOP FOREACH CurrentItem IN ExcelData
Display.ShowMessageDialog.ShowMessage Message: $'''%CurrentItem[0]%
%CurrentItem[1]%
%CurrentItem[2]%''' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed
END
Steps
Put in place "Launch Excel".
| Parameter | Value |
|---|---|
| Launch Excel | and open the following document |
| Document path | Target file |
Put in place "Set active Excel worksheet".
Specify the sheet to read.
Put in place "Read from Excel worksheet".
| Parameter | Value | Remarks |
|---|---|---|
| Excel instance | and open the following document | |
| Retreive | All available values from worksheet | |
| First line of range contains column names | ON | Off if the first row of the table is not a heading but a data row. |
Put in place "Close Excel".
Next put "For each" in place and set the "Value to iterate" to %ExcelData% (Variable produced by "Read from Excel worksheet").
Put in place an action in "For each" that runs one line at a time.
One row of data is stored in the %CurrentItem% variables produced.
If "Read from Excel worksheet" "First line of range contains column names" is ON, then the individual values are retrieved by item name as follows.
%CurrentItem['No']% %CurrentItem['Name']% %CurrentItem['Num']%
When "First line of range contains column names" is OFF, the values of individual columns are obtained by sequential numbering with the first digit as 0 as follows.
Even if "First line of range contains column names" is ON, the sequential numbering can be used.
%CurrentItem[0]% %CurrentItem[1]% %CurrentItem[2]%
For those who want to learn Power Automate Desktop effectively
The information on this site is now available in an easy-to-read e-book format.
Or Kindle Unlimited (unlimited reading).
You willl discover how to about basic operations.
By the end of this book, you will be equipped with the knowledge you need to use Power Automate Desktop to streamline your workflow.