Apply Excel filter and save the file

Help Studio
, , ,
1

Hi,

I have two unique requirements from a client

  1. Put a filter on an excel file and save the file with that filtered result.

Example: I need to put filter on a column country. After applying the filter, save the file. So that if anybody opens it manually, then they should get the filtered result directly.

  1. Activate a sheet and save the file. So that if anybody opens the excel file they should directly see that sheet.

Regards,
Vishal.

2

@Vishal_Kumar4

yes what you are expecting

3

how to achieve this? let me know if you want me to repharese the question.

4

@Vishal_Kumar4

Use Read Range activity

2.Use Filter datatable and create the output to the same output variable of read range

3.use write range activity giving the same excel path ,then the file will be in the new filtered format.

If you want any thing mention

5

My bad to explain the requirement.

Basically i want to keep all data in excel file. But after automation it has to be in a filtered state. See screenshot for reference.

Before Automation, the file will look like this. it will show all data.

1
1931×174 5.76 KB

After automation.

2
2931×174 4.96 KB

After automation if i open the file i should have a filter applied to the file and only filtered results should be visible. But also the rest of the other data should not be deleted.

imo it’s a strange ask.

6

@Vishal_Kumar4

Its possible by making the filtered datatable into a separate sheet of same excel file or in the same sheet of input excel by making old data into separate sheet.

  1. you are saying that the old data should not be deleted means it has to be in the same excel or store it into a separate sheet
7

Ok just assume that you have an excel file with you with above data. Now you applied the filters manually for the location column and then you saved and closed the file. Next time if you open the file, it will open with the filtered result.

similarly this needs to be done by bot. I just want the filter applied on the same sheet. the filter option should be visible in the column.

8

@Vishal_Kumar4

ya got it

use the below steps You will get the above mentioned output with modern activities

image
image896×739 26.9 KB

w

cheers

9

Ok I will try and let update.

10

@Vishal_Kumar4

ok Thanks

Cheers

11

My project is build classic activity.
can’t use above option

regards

12

@Vishal_Kumar4

use read range activity

2.filter activity

3.write range activity writing back to the same excel file

cheers

13

But this will remove filtered data

14

@Vishal_Kumar4

yes by using modern activities also the old data will be gone

only the filtered data will be shown when you open the excel

cheers

15

Can it be done by VB.Net?

16

@Vishal_Kumar4

no idea

17

I did it by Excel Macro.

18

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