Power Automate Desktop - Consolidated Excel Automation (Complete Flow Explanation)

Power Automate Desktop - Consolidated Excel Automation (Complete Flow Explanation)

This desktop flow consolidates multiple Excel files into one master Excel file and emails the final report to a chosen recipient. The flow consists of three major blocks: User Input Collection, Main Consolidation Loop, and Read & Write Subflow. Below is the clear explanation of each block.

🔹 Block 1 - Main Flow Controller

This block initializes the flow, prepares the environment, loops through all Excel files, consolidates the content, and finally sends the output by email.

  • Create New List: A blank list variable is created to store Excel file paths.
  • Get Excel Files Details: A subflow populates the list based on user input.
  • Get Documents Folder Path: Determines where the consolidated report will be saved.
  • Launch Master Excel: Opens a blank Excel instance that acts as the final consolidated workbook.
  • Loop Through File List: For every file in the list, the subflow Read_Write_Excel_Data is executed to append its data into the master workbook.
  • Save Consolidated Report: The final workbook is saved to Documents\Consolidated Report.xlsx.
  • Convert to Base64: Required for adding the file as an attachment.
  • Send Email Using Outlook: Sends the final Excel file to the recipient entered by the user.
  • Display Success Message: Confirms location of the saved file and the email sent.
Note: Change the send an email action connection reference to your user account.

🔹 Block 2 - User Input & File Selection(Sub flow: Get_Excel_Files_Details)

This block gathers all required input from the user via Adaptive Cards.

✔ Step 1 – Initial File Selection Form

The user is asked to:

  • Select the first Excel file.
  • Select the second Excel file.
  • Choose whether they want to add more files.
  • Enter recipient email address.
  • Enter their own Outlook email address.

These values are stored in variables. The first two file paths are added into the List variable.

✔ Step 2 – Dynamic Loop to Add More Files

If the user chooses "Yes, I want to select more files", a smaller popup form appears repeatedly.

Each loop asks the user to:

  • Select one more Excel file.
  • Choose whether to add another file.

Every file selected in this loop is appended to the List until the user selects "No".

✔ Step 3 – Cancel Handling

If the user presses Cancel at any point, the flow exits with a clean message.

🔹 Block 3 – Read & Write Subflow (Read_Write_Excel_Data)

This subflow is called once for each file during the loop. It reads the contents of a file and appends it to the master workbook.

  • Launch Excel File: Opens the Excel file at the current index of the list.
  • Read All Cells: Retrieves all data from the sheet using “FormattedTextValues”.
  • Close Input Excel: Closes the file after reading to avoid locking.
  • Find First Empty Row: Checks the master workbook to find where new data should be appended.
  • Write Data: Pastes the extracted data starting at the first free row in Column A.

This process repeats for every Excel file selected by the user, resulting in a combined dataset.

You can access the flow code by clicking the link below.

Power-Automate-Desktop/Consolidated Excel at main · sunilshetty07/Power-Automate-Desktop



📌 Final Summary

This Power Automate Desktop process:

  • Collects multiple Excel files from the user
  • Appends all data into a single consolidated workbook
  • Saves the final report to the user's Documents folder
  • Emails the final consolidated file automatically

The combination of dynamic user input, looping, subflows, Excel reading/writing, and Outlook integration makes this automation a complete end-to-end consolidation solution.

Comments

Post a Comment

Popular posts from this blog

Part 1: Creating Code Apps in Power Apps - A step-by-step guide (with real errors I faced & how I fixed them)

Image
Intro In this post I’ll walk you through how I set up a code-based app for Power Apps (React/Vite front-end + Power Platform CLI for packaging/deployment), the exact errors I ran into, and how I solved them. If you want a hands-on, copy-paste able guide that covers Node versions, pac auth, environment issues, and packaging tips, this is for you. Prerequisites Microsoft 365 account with access to a Power Platform environment Node.js ( 20.19+  recommended LTS) VS Code Install power platform tools extension in VS Code. Steps: Step 1 : Open Visual Studio Code and launch the integrated terminal. Step 2: In the terminal, run the following commands one by one.   mkdir D:\CodeApps - Force cd D:\CodeApps npm create vite @latest myfirstapp -- -- template react - ts cd D:\CodeApps\myfirstapp npm install Initially, I ran the command: npm create vite@latest MyFirstApp -- --template react-ts This gave the error: Invalid package.json name The issue was caused because package...
Read more

Calling Microsoft Graph API from Power Automate Using Azure App Services – Step-by-Step Guide

Image
Step 1: You should have an Azure subscription, or if you have access to the Microsoft Entra Admin Center, you can create an app from there. This app can then be used in Power Automate to connect to Microsoft Graph API. Step 2: Click on "New registration" , provide a name for your app, assign the required API permissions (such as Microsoft Graph permissions), and then click "Register" to complete the app registration process. Step 3: After registration, you'll be redirected to the app's overview page. From there, navigate to the API permissions tab and click "Add a permission" to assign the necessary Microsoft Graph API permissions. Step 4: Select Microsoft Graph as the API, and based on your requirements, choose the appropriate permissions. In my case, I selected Application permissions , which do not require user sign-in and are suitable for background services or automation. Step 5: Select the required Microsoft Graph API permissions. Yo...
Read more

Step-by-Step Guide: Power Automate Custom Connector Using Graph API from Azure App Service

Image
Step 1: Open Power Automate, navigate to the Custom Connectors tab, and select Create from blank as shown in the image below. custom connector(click image for better resolution) Step 2: In the General tab, upload an icon (less than 1MB), add a description, select the correct scheme (in my case, HTTPS ), and provide the host name. For example, if your API URL is https://graph.microsoft.com/v1.0/users/{email} , the host name will be the highlighted part — graph.microsoft.com . Step 3: Go to the Security tab. Here, you need to select the authentication method. Choose OAuth 2.0 as the authentication type, as shown in the image below. Step 4: Select the Identity Provider as Azure Active Directory . Check the box Enable Service Principal Support . This option allows you to create a connection using a Client ID and Client Secret instead of using the user's own authentication. Next, provide the Client ID , Client Secret , Tenant ID , and other required details as shown in t...
Read more