Hi All ,
i want to filter data from an excel sheet. i am aware of the data table and filter concept in it. but my compulsion is i need to do via UI only ,
why this compulsion ? because after this filter set a set of excel macros that will get applied on this sheet. so if i read in Data table i need to write back in excel etc ., and there are lots of colour formatting which i might loose. so dealing with UI is the best option .
can i edit those macros ? Nope , those macros itself a burden now , again editing is something our CoE team said they wont approve.
can i write a new macro ? again , adding more macros is not entertained . so i need to deal with UI only.
now can any one help me in this ? @loginerror
Hey @Seetharaman_K,
Hope you are well.
Do you have filters on the headers within the Excel sheet at the moment? I would use those filters. Does that solve your problem or are you hoping to remove any rows that don’t meet your filter requirement?
Yes , i want to delete the rows which comes as a result of the filter.
and when BOT opens the excel sheet , filters may or may not be there… its better clear any filters and then apply the filter.
am not sure whether i understood ur question fully
@Seetharaman_K If you can provide us a Sample of your Input Data and the Output that you would need after Filtering, We might just be able to do it with Excel Application Scope Activities to preserve the formats. Although we might have to read the data and get the Datatable, it will just be to find the indices or values where it resides and perform actual operations in the Excel Sheet itself.
In this way there is no Complete dependency on UI actions, it can be performed in the background as well.
AuthBuyerList.xlsx (43.8 KB)
what i need to do with this file ?
select row number 5
go to data ribbon
click filter , apply filter on column J ,
filter criteria , any thing which has a ’ date ’
once we filter all these records needs to be deleted.
@Seetharaman_K I have managed to get the workflow working, But the Strange thing what I had found was that it needs a checking again to see if there are any rows having dates in “DE Activation” Column and then Perform the Operation again, that’s why I had to use two Loops, One Do while that determines whether the Column is free from dates after removing and Another For Each loop to remove the rows containing the dates.
Check the workflow :
I have kept a Copy of the File as well, Just so you can check the difference between the original and the Excel after deleting rows.
Remove Unwanted Rows.zip (68.1 KB)
You can Check the Excel Files. Then Execute the workflow. It will take around 2 mins to complete, it might be longer if there are more rows. Also revert back if found any errors.
@Seetharaman_K Can you update your packages to the latest version and check ? Also What version of Studio are you using ?
uipath studio version 19.4.3
uipath.activities version 19.10
if u can say which poackage excatly i can do a check
it is clearly saying , interruptible do while , is it normal do while or something different?
@Seetharaman_K I developed it in Community Version 20.4 
, So all the Packages need to be atleast the versions mentioned in the below image or greater than that.
Of course, the workflow won’t need the Mail and UiAutomation Activities. It is the normal do while loop.
actually u are right , i am going to update the version . i was under impression that an upadated uiapth will have latest package.
ok errors are cleared. Now ,
but the thing is u played with the data table , which is not exactly what i was asking. i was asking to do the same filter using the UI of EXCEL software… 
anyway , this also work only if the team agrees to loose the formatting of the sheet which will be lost after the DT operations. will see, i really appriciate ur effort.
one more drawback is , it is done using latest version i suppose the same logic can be done in our authorized enterprise version of 19.4 , if i use this.
@Seetharaman_K As I have told you earlier playing with datatable was just to get the row value indices, then delete those rows in the Excel Sheet itself. Hence the formatting should be maintained, but confirm it and let me know if it doesn’t work
not just formatting , as i explained in my requirement , post this action there are macros which is going to operate on this sheet. which may fail . anyway i will give a try.
@Seetharaman_K I would have to redo the same workflow in a different version and send you if it works
nono , dont put effort on this. am doing it. i should be more courteous to u of what u have done so far. am just wondering , the sheet must be having NAME-First and NAme-last columns twice , how do the read range worked. ?? for me its throwing duplicate column name error
am planning to un check the header and the bot will give a column name itself.
ashokkarale
Anil_G
Yoichi
yangyq10
postwick
chandreshsinh.jadeja
aravindbalineni123
Parvathy
aya
PRASHANT_GABHANE