Top
Photo by Jakob Owens on Unsplash

Filter Excel table using Power Automate Desktop


In this post I will show you how you can filter data inside a table in Microsoft Excel.

The process is really simple and easy. It all relates on action called “Send keys” with configuration telling the action to send them to a specific window. First, when you open Excel file, you need to turn on filtering. This can be done by sending keyboard shortcut Ctrl+Shift+L. This in PAD should be expressed as {Control}({Shift}(L)).

Next you need to put a focus on a cell, that contains header for the column you want to filter (or in other words – where the small button to open filter dialog is visible).

Once focus is set, you need then to send another keyboard shortcut, this time: Alt+Arrow down. In PAD it would be: {Alt}({Down}).

Then use “UI selector” to get UI elements of the specific parts of the filter dialog box, eg. search box and OK button. Once you have them, simply build logic around them, like populating search box with a term you need to use for filtering and then pressing OK button.

And that’s it!

Below you can find a code, that once you copy and paste to your PAD will turn into 7 actions together with selectors, so that you can try this on your own. Good luck!

Excel.LaunchExcel.LaunchAndOpen Path: $'''C:\\Users\\USER\\Downloads\\book.xlsx''' Visible: True ReadOnly: False LoadAddInsAndMacros: False Instance=> ExcelInstance
Excel.SelectCellsFromExcel.SelectCells Instance: ExcelInstance StartColumn: $'''A''' StartRow: 1 EndColumn: $'''A''' EndRow: 1
MouseAndKeyboard.SendKeys.FocusAndSendKeysByInstanceOrHandle WindowInstance: ExcelInstance TextToSend: $'''{Control}({Shift}(L))''' DelayBetweenKeystrokes: 10 SendTextAsHardwareKeys: True
Excel.SelectCellsFromExcel.SelectCells Instance: ExcelInstance StartColumn: $'''B''' StartRow: 1 EndColumn: $'''B''' EndRow: 1
MouseAndKeyboard.SendKeys.FocusAndSendKeysByInstanceOrHandle WindowInstance: ExcelInstance TextToSend: $'''{Alt}({Down})''' DelayBetweenKeystrokes: 10 SendTextAsHardwareKeys: True
UIAutomation.PopulateTextField TextField: appmask['Window \'book.xlsx - Excel\'']['Edit \'Type field name to search for\''] Text: 2 Mode: UIAutomation.PopulateTextMode.Replace ClickType: UIAutomation.PopulateMouseClickType.SingleClick
UIAutomation.PressButton Button: appmask['Window \'book.xlsx - Excel\'']['Button \'OK\'']

# [ControlRepository][PowerAutomateDesktop]
{
  "ApplicationInfo": {
    "Name": "ClipboardControlRepository",
    "Version": "1.0"
  },
  "Screens": [
    {
      "Controls": [
        {
          "AutomationProtocol": "uia3",
          "ElementTypeName": "Edit",
          "InstanceId": "85bbf960-d360-4df5-97e7-49f73a6e04fe",
          "Name": "Edit 'Type field name to search for'",
          "SelectorCount": 1,
          "Selectors": [
            {
              "CustomSelector": null,
              "Elements": [
                {
                  "Attributes": [
                    {
                      "Ignore": false,
                      "IsOrdinal": false,
                      "Name": "Class",
                      "Operation": "EqualTo",
                      "Value": "NetUIToolWindow"
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Enabled",
                      "Operation": "EqualTo",
                      "Value": true
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Id",
                      "Operation": "EqualTo",
                      "Value": ""
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Name",
                      "Operation": "EqualTo",
                      "Value": ""
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": true,
                      "Name": "Ordinal",
                      "Operation": "EqualTo",
                      "Value": -1
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Visible",
                      "Operation": "EqualTo",
                      "Value": true
                    }
                  ],
                  "CustomValue": null,
                  "Ignore": false,
                  "Name": "Menu 'NetUIToolWindow'",
                  "Tag": "menu"
                },
                {
                  "Attributes": [
                    {
                      "Ignore": false,
                      "IsOrdinal": false,
                      "Name": "Class",
                      "Operation": "EqualTo",
                      "Value": "NetUIDismissBehavior"
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Enabled",
                      "Operation": "EqualTo",
                      "Value": true
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Id",
                      "Operation": "EqualTo",
                      "Value": ""
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Name",
                      "Operation": "EqualTo",
                      "Value": ""
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": true,
                      "Name": "Ordinal",
                      "Operation": "EqualTo",
                      "Value": -1
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Visible",
                      "Operation": "EqualTo",
                      "Value": true
                    }
                  ],
                  "CustomValue": null,
                  "Ignore": false,
                  "Name": "UI Custom 'NetUIDismissBehavior'",
                  "Tag": "custom"
                },
                {
                  "Attributes": [
                    {
                      "Ignore": false,
                      "IsOrdinal": false,
                      "Name": "Class",
                      "Operation": "EqualTo",
                      "Value": "NetUITextbox"
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Enabled",
                      "Operation": "EqualTo",
                      "Value": true
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Id",
                      "Operation": "EqualTo",
                      "Value": ""
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Name",
                      "Operation": "EqualTo",
                      "Value": "Type field name to search for"
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": true,
                      "Name": "Ordinal",
                      "Operation": "EqualTo",
                      "Value": -1
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Visible",
                      "Operation": "EqualTo",
                      "Value": true
                    }
                  ],
                  "CustomValue": null,
                  "Ignore": false,
                  "Name": "Edit 'Type field name to search for'",
                  "Tag": "edit"
                }
              ],
              "Ignore": false,
              "IsCustom": false,
              "IsWindowsInstance": false,
              "Order": 0
            }
          ],
          "Tag": "edit"
        },
        {
          "AutomationProtocol": "uia3",
          "ElementTypeName": "Button",
          "InstanceId": "509b6bbb-8b91-4426-81a1-459b9be42b3e",
          "Name": "Button 'OK'",
          "SelectorCount": 1,
          "Selectors": [
            {
              "CustomSelector": null,
              "Elements": [
                {
                  "Attributes": [
                    {
                      "Ignore": false,
                      "IsOrdinal": false,
                      "Name": "Class",
                      "Operation": "EqualTo",
                      "Value": "NetUIToolWindow"
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Enabled",
                      "Operation": "EqualTo",
                      "Value": true
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Id",
                      "Operation": "EqualTo",
                      "Value": ""
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Name",
                      "Operation": "EqualTo",
                      "Value": ""
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": true,
                      "Name": "Ordinal",
                      "Operation": "EqualTo",
                      "Value": -1
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Visible",
                      "Operation": "EqualTo",
                      "Value": true
                    }
                  ],
                  "CustomValue": null,
                  "Ignore": false,
                  "Name": "Menu 'NetUIToolWindow'",
                  "Tag": "menu"
                },
                {
                  "Attributes": [
                    {
                      "Ignore": false,
                      "IsOrdinal": false,
                      "Name": "Class",
                      "Operation": "EqualTo",
                      "Value": "NetUIDismissBehavior"
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Enabled",
                      "Operation": "EqualTo",
                      "Value": true
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Id",
                      "Operation": "EqualTo",
                      "Value": ""
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Name",
                      "Operation": "EqualTo",
                      "Value": ""
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": true,
                      "Name": "Ordinal",
                      "Operation": "EqualTo",
                      "Value": -1
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Visible",
                      "Operation": "EqualTo",
                      "Value": true
                    }
                  ],
                  "CustomValue": null,
                  "Ignore": false,
                  "Name": "UI Custom 'NetUIDismissBehavior'",
                  "Tag": "custom"
                },
                {
                  "Attributes": [
                    {
                      "Ignore": false,
                      "IsOrdinal": false,
                      "Name": "Class",
                      "Operation": "EqualTo",
                      "Value": "NetUIButton"
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Enabled",
                      "Operation": "EqualTo",
                      "Value": true
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Id",
                      "Operation": "EqualTo",
                      "Value": ""
                    },
                    {
                      "Ignore": false,
                      "IsOrdinal": false,
                      "Name": "Name",
                      "Operation": "EqualTo",
                      "Value": "OK"
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": true,
                      "Name": "Ordinal",
                      "Operation": "EqualTo",
                      "Value": -1
                    },
                    {
                      "Ignore": true,
                      "IsOrdinal": false,
                      "Name": "Visible",
                      "Operation": "EqualTo",
                      "Value": true
                    }
                  ],
                  "CustomValue": null,
                  "Ignore": false,
                  "Name": "Button 'OK'",
                  "Tag": "button"
                }
              ],
              "Ignore": false,
              "IsCustom": false,
              "IsWindowsInstance": false,
              "Order": 0
            }
          ],
          "Tag": "button"
        }
      ],
      "ElementTypeName": "Window",
      "InstanceId": "2497b5ce-8aac-4541-b166-85d9d2dfed34",
      "Name": "Window 'book.xlsx - Excel'",
      "SelectorCount": 1,
      "Selectors": [
        {
          "CustomSelector": null,
          "Elements": [
            {
              "Attributes": [
                {
                  "Ignore": true,
                  "IsOrdinal": false,
                  "Name": "Class",
                  "Operation": "EqualTo",
                  "Value": "XLMAIN"
                },
                {
                  "Ignore": true,
                  "IsOrdinal": false,
                  "Name": "Enabled",
                  "Operation": "EqualTo",
                  "Value": true
                },
                {
                  "Ignore": true,
                  "IsOrdinal": false,
                  "Name": "Id",
                  "Operation": "EqualTo",
                  "Value": ""
                },
                {
                  "Ignore": false,
                  "IsOrdinal": false,
                  "Name": "Name",
                  "Operation": "EqualTo",
                  "Value": "book.xlsx - Excel"
                },
                {
                  "Ignore": true,
                  "IsOrdinal": true,
                  "Name": "Ordinal",
                  "Operation": "EqualTo",
                  "Value": -1
                },
                {
                  "Ignore": false,
                  "IsOrdinal": false,
                  "Name": "Process",
                  "Operation": "EqualTo",
                  "Value": "EXCEL"
                },
                {
                  "Ignore": true,
                  "IsOrdinal": false,
                  "Name": "Visible",
                  "Operation": "EqualTo",
                  "Value": true
                }
              ],
              "CustomValue": null,
              "Ignore": false,
              "Name": "Window 'book.xlsx - Excel'",
              "Tag": "window"
            }
          ],
          "Ignore": false,
          "IsCustom": false,
          "IsWindowsInstance": false,
          "Order": 0
        }
      ],
      "Tag": "window"
    }
  ],
  "Version": 1
}

Tomasz Poszytek

Hi, I am Tomasz. I am expert in the field of process automation and business solutions' building using Power Platform. I am Microsoft MVP and Nintex vTE.

No Comments

Post a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.