With all due respect, automating the Excel UI is a terrible suggestion.
@JamesRPA , if what you want to do is to retrieve filtered data, so you can do some further processing in your flow, you can do that by:
a) Using a SQL query to Excel. See more info here: https://learn.microsoft.com/en-us/power-automate/desktop-flows/how-to/sql-queries-excel
b) Reading the entire data from Excel using the normal Excel actions and then looping through the data to add only relevant rows to a new data table you created (much less efficient than a) ).
If you want your Excel file to be filtered (for cases when you don't need the data itself, but maybe need to generate a report or something), the best approach is to use scripting. Use VBscript or PowerShell scripts to open the document, apply AutoFilter to the range you want, set the filter value, save the document and close Excel. This is quite easy to Google or even ask ChatGPT to write for you.
Here's a sample:
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
Set objWorkbook = objExcel.Workbooks.Open("C:\path\to\sample.xlsx") ' Replace with the path to your sample Excel document
Set objWorksheet = objWorkbook.Worksheets(1) ' Sets the first sheet as the active sheet
Set objRange = objWorksheet.UsedRange ' Replace with the range you want to apply the auto filter to by using .Range("A1:D100") instead of .UsedRange for example
objRange.AutoFilter ' Applies auto filter to the range
' Set a value to filter by on column 1 in the range
objRange.AutoFilter _
Field:=1, _
Criteria1:="FilterValue",
objWorkbook.Save ' Saves the document
objWorkbook.Close ' Closes the workbook
objExcel.Quit ' Quits Excel
See more parameters for the Range.AutoFilter method in VBscript here: https://learn.microsoft.com/en-us/office/vba/api/excel.range.autofilter
-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution.
If you like my response, please give it a Thumbs Up.
If you are interested in Power Automate, you might want to follow me on LinkedIn at https://www.linkedin.com/in/agnius-bartninkas/
