Power Automate Desktop - Excel Find & Replace Automation

Power Automate Desktop - Excel Find & Replace Automation

This desktop flow allows the user to select an Excel file and perform a Find & Replace operation on the contents of the worksheet. The user can choose whether to replace all occurrences of a value or only the first occurrence.

1. Display Custom Form

The flow begins by opening a custom Adaptive Card form. The form contains fields where the user can:

  • Enter the text to find
  • Enter the replacement text
  • Select an Excel file (xls, xlsx, csv)
  • Choose the replace mode: Find & replace all or Find & replace first

2. Check for Cancel

If the user presses Cancel, the flow stops immediately. Otherwise, it continues with the replacement process.

3. Launch Excel

The selected Excel file is opened in editable mode using the Launch Excel Under Existing Process action. This ensures the file is opened inside an already running Excel instance.

4. Perform Find & Replace

The flow checks the user’s selection:

  • If the user selected "Find & replace all" The action FindAndReplaceAll updates all matching text in the worksheet.
  • If the user selected "Find & replace first" The action FindAndReplaceSingle updates only the first matched value.

5. Save & Close Excel

After completing the replacement process, the Excel file is saved and closed using CloseAndSave.


Complete PAD Code Used

# The following action displays a custom form and prompts you to select a file, the values you want to replace, and the values you want to replace them with.
@@statistics_TextBlock: '1'
@@statistics_Input_Text: '2'
@@statistics_Input_File: '1'
@@statistics_Input_ChoiceSet: '1'
@@statistics_Action_Submit: '2'
Display.ShowCustomDialog CardTemplateJson: '''{
  \"type\": \"AdaptiveCard\",
  \"version\": \"1.4\",
  \"id\": \"AdaptiveCard\",
  \"body\": [
    {
      \"type\": \"TextBlock\",
      \"id\": \"Text block\",
      \"text\": \"${Text_block_Text}\",
      \"wrap\": true
    },
    {
      \"type\": \"Input.Text\",
      \"id\": \"TextToFind\",
      \"isRequired\": true,
      \"label\": \"${TextToFind_Label}\"
    },
    {
      \"type\": \"Input.Text\",
      \"id\": \"NewText\",
      \"isRequired\": true,
      \"label\": \"${NewText_Label}\"
    },
    {
      \"type\": \"Input.File\",
      \"id\": \"File input\",
      \"value\": \"\",
      \"isRequired\": true,
      \"label\": \"${File_input_Label}\",
      \"errorMessage\": \"${File_input_ErrorMessage}\"
    },
    {
      \"type\": \"Input.ChoiceSet\",
      \"id\": \"RenameFunction\",
      \"value\": \"${RenameFunction_Value}\",
      \"style\": \"compact\",
      \"isMultiSelect\": false,
      \"choices\": [
        {
          \"title\": \"Find & replace all\",
          \"value\": \"all\"
        },
        {
          \"title\": \"Find & replace first\",
          \"value\": \"first\"
        }
      ]
    }
  ],
  \"actions\": [
    {
      \"type\": \"Action.Submit\",
      \"id\": \"Submit\",
      \"title\": \"${Submit_Title}\"
    },
    {
      \"type\": \"Action.Submit\",
      \"id\": \"Cancel\",
      \"associatedInputs\": \"none\",
      \"title\": \"${Cancel_Title}\",
      \"IsCancel\": true
    }
  ]
}''' CustomFormData=> CustomFormData ButtonPressed=> ButtonPressed @Text_block_Text: $'''This desktop flow searches and replaces the provided values from the selected worksheet. Note that the edited file will be saved.''' @TextToFind_Label: $'''Text to find: ''' @NewText_Label: $'''Text to replace with:''' @File_input_Label: $'''Please select the Excel file (*.xls,*.xlsx,*.csv) you want to search text in: ''' @File_input_ErrorMessage: $'''Please provide a path to the Excel file. ''' @RenameFunction_Value: $'''all''' @Submit_Title: $'''Replace & save''' @Cancel_Title: $'''Cancel'''

# The following 'If' action checks whether the 'Cancel' button was pressed in the custom form. If not, the flow replaces the provided values.
IF ButtonPressed <> $'''Cancel''' THEN
    Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: CustomFormData['File input'] Visible: True ReadOnly: False Instance=> ExcelInstance

    # The following 'If' block checks the selected renaming function in the custom form and performs the appropriate renaming method in the Excel file.
    IF CustomFormData['RenameFunction'] = $'''all''' THEN
        Excel.FindAndReplace.FindAndReplaceAll Instance: ExcelInstance TextToFind: CustomFormData['TextToFind'] TextToReplaceWith: CustomFormData['NewText'] MatchCase: False MatchEntireCellContents: False SearchBy: Excel.SearchOrder.Rows Cells=> Cells
    ELSE IF CustomFormData['RenameFunction'] = $'''first''' THEN
        Excel.FindAndReplace.FindAndReplaceSingle Instance: ExcelInstance TextToFind: CustomFormData['TextToFind'] TextToReplaceWith: CustomFormData['NewText'] MatchCase: False MatchEntireCellContents: False SearchBy: Excel.SearchOrder.Rows FoundColumnIndex=> FoundColumnIndex FoundRowIndex=> FoundRowIndex
    END

    Excel.CloseExcel.CloseAndSave Instance: ExcelInstance
END

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