Switch off the Filter in an excel

Help
1

Hi All,
I need to Switch off the Filter in an excel if Filter is applied in that Excel, Else nothing to perform.
So My question is How to Use If else Statement for that Excel for Filter??
Could Any one please suggest?

1 Like
2

@keshav,

Can you please share me your logic so that i can give a solution for that. meanwhile, I have created one sample workflow with filter the duplicate name in Excel file using if else statement inside For each activity (Without Filter Activity).
DuplicateRecordsCheck.zip (213.1 KB)

Thanks,
Arunachalam.

1 Like
3

I think the requirement is to remove the filter in an excel worksheet if it is active?

So you would need to first check if the filter is active on the worksheet, then if it is remove it?

Take a look at FilterMode in Vb.net to check if it is filtered or not.

1 Like
4

@Arunachalam
Thank you very much Arun.
My Logic is I am getting an Excel, Sometime in that sheet Filter is active and sometimes filter is not active. So If filter is active I need to Switch off the Filter and If Filter is not Active No need to do anything.

5

Thank You TimK.

Can it be done by UI path activity??

6

@keshav,

I Hope if the sheet in active filter mode, our bot will extract the entire data of an excel. I have created one sample example with active filter by name. it will get all the data even if your using Active filter sheet

ExcelFilter.zip (7.7 KB)

Please check while Read Range Activity unChecked The UseFilter . This may help give solution for your problem.

image
image926Ă—484 18 KB

Thanks,
Arunachalam.

1 Like
7

@keshav its very simple, one click can solve this

yes…whenever you read a excel file with open application scope and followed by read range activity, we have an option in read range activity called “USE FILTER” property…this will be usually diabled,so when you read file with read range being this property disabled no need to worry about filters, it wont take those…but if it is enabled it will take the filter applied…

there is no need to have if condition for this,because you are done while reading the excel itself…

Thats all,just a single click…isn’t it

Hope this would help you

Cheers…

8

Thank you Very Much Arun. I got It.

Thanks Again for your valuable time.

Regards,
Keshav

9

@Palaniyappan
Thank you Very Much Palaniyappan.

Thanks Again for your valuable time.

Regards,
Keshav

1 Like
10

hi @Palaniyappan,

For reading filtered record when this filter is unchecked it works, however if i want to delete them (filtered records) using delete range, is there a workaround available ?

1 Like
11

Yah there are many ways

may be we can fetch the filtered records as one datatable dt1 with excel application scope and read range enabling use filters

And
Another read range with all records As dt2 without use filters

With Join datatable activity we can get the records we need and write back to the excel With WRITE RANGE activity so that it will over write the existing one

Or
We can do this with macros as well by recording the manual steps with Record Macro option in View tab
This will give us the vb script which can be copied and pasted in txt file and run that with Invoke VBA activity
Cheers @ANSHUL

12

Hi @Palaniyappan ,

I am using Delete Range activity to delete the records and noticed the filtered records were not deleted, I used Macro to remove filter as below:

Public Sub KillFilter()
If ActiveSheet.AutoFilterMode Then
ActiveSheet.AutoFilterMode = False
End If
End Sub

Public Sub StartFilter()
If Not ActiveSheet.AutoFilterMode Then
ActiveSheet.Range(“A1”).AutoFilter
End If
End Sub

and used invoke VBA, to call the above functions and it works fine.
Thanks Man :slight_smile:

2 Likes
13

Cheers @ANSHUL

1 Like
14

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.