Filtering excel sheet through Workfusion

Hi, I want to manipulate an excel sheet by using the filter option for a number of locations that are dynamic, and select or unselect the wanted data whenever is necessary.
The code would be running every day and it should filter 3 different column. The bot will filter 1st column and then 2nd one and then the 3rd, in which suppose it will select the department first in which the data will not be same everyday so as in the 2nd and 3rd column. The bot has to select more than one option today in the 1st column, it might have to choose 4 data tomorrow or it might have to choose none the day after tomorrow. Also, filtering one column will also affect the other columns. So is there any way that we can make a bot select more than two data or unselect more than two data, which are dynamic completely?

Try opening the excel file on the screen and filtering the data using object selectors and keystrokes:

  • click on the first cell in the column
  • press Ctrl+Shift+L to apply filters (only for 1 column)
  • Alt+Down to open the the filter popup
  • uncheck “Select All” by clicking on selector [CLASS:CheckBox; NAME:(Select All); TEXT:(Select All)]
  • select all data you need to filter by clicking on this selector in For Each loop: [CLASS:CheckBox; NAME:${element}]
  • press Enter

Repeat it for each column you need to filter.

Then select the resulting table, copy it and save to a table variable using Clipboard action.
Note: if you copy data from Excel, you need to use Linux / MacOS (LF) as rows separator.

You can store the data, by which you need to filter 3 columns, in 3 list variables.

Here is a sample script of how you can do it.

filter-excel.zip (256.9 KB)

2 Likes

Here’s a test data file. too.
regions.xlsx (14.7 KB)

Hi, thank you for the feedback, however i don’t want to record my actions as that gives me a lot of mouse click errors. I want to input the required steps into the action flow manually.

For example: To open an excel sheet
Then filter a certain column
Then copy the filtered data(Table) into a separate excel sheet.

Another problem i was having is with pasting a range of data from excel to another excel sheet.

Hi @trisha you can add these actions to the actions flow manually, no need to record them.

To copy-paste a table in Excel, you can select and copy it using keystrokes ((Ctrl+Shift+{END}), then Ctrl+V), switch to the required sheet and paste it there using Ctrl+V.

Hi, i tried several times however it fails to run.Even the filter keystrokes(Ctrl+shift+L) does not work.

What error do you get? Did you switch to the Excel window before using keystrokes there?

Hi, thank you for the help :slightly_smiling_face:, I managed to extract rows rather than filter. However now I need to copy data from various excel sheet onto one main sheet the problem is setting the range for the second table to be pasted below the first one that has been pasted, since I’m working with a lot of data its impossible to know the specific cell to paste into. Is it possible to iterate to find the the last row that is empty in the final table then paste data into it?

below is an example:image
Table one from excel sheet 1.

image
Table 2 from excel sheet 2

Final results i want :image

@trisha glad you made it work :slightly_smiling_face:
This topic should help you to insert the data dynamically

1 Like

Hi Ashapkina,

I had tried your steps based on this discussion: https://forum-2-new.workfusion.com/t/filtering-excel-sheet-through-workfusion/57388


However, the keystrokes (Enter) doesn’t work and it just continuing the steps without prompting any error message.

Hi @Jingle you need to either switch to the specific window before typing anything in it (using Window action) or enable this setting in the Preferences.

1 Like

Thanks for your help! It’s working now :slight_smile:

1 Like

Glad it helped :slight_smile: