Excel filter

Questions & Suggestions
1

Hey all,

While still learning so much about KM every day, I feel encouraged to tackle some of my more complex manual tasks with macros. I literally cannot stop to continuously try out more and more macros. Sound familiar? :smiley:

I just created a workflow with more than 150 different steps that downloads 2 excel reports and filters the content to then copy it into an e-mail. While it works really well, there is one aspect within the Excel file that could be improved. Maybe someone here has a better idea on how to do it.

When I open the excel file, there is a particular column I need to filter. It’s a list of names. When at least one of the names I am looking for is in the table, than all is good. Sometimes however, the relevant names are not included in one of the reports. In this case the current macro results in an unfiltered list to be copied into the e-mail. What I would rather see is that either an empty list will be copied into the e-mail or, even better, nothing will be copied.

When the filter does not show the desired results, it looks like the screenshot. The current workflow looks for the image of the button “Apply Filter” and clicks it, resulting in an unfiltered list.

image

Worth mentioning: There is two different excel files that will be copied into the e-mail. At least one of them will always have relevant content. So the workflow should not be completely interrupted if one file does not have the names in it. Hence copying an empty table would be OK.

Thank you for your ideas :slight_smile:

2

What version of Excel are you using?

3

Simplest, given your current workflow, is to precede your "Click the 'Apply' button" action with a check for the image

image

...and if that image is present do your "nothing found" stuff, otherwise do "normal" stuff

4

Thank you! Check for image was new to me. Still struggle to make it work. This is how I set it up:

image
image864×944 105 KB

It does find the image but than acts according to the "else" peace in the if action. Any idea why?

5

16.90

6

Basically, it's failing because Find Image Onscreen is not the same as OCR Image. You've set a variable to the value of the found image, but that value will not be the text in the image. Instead, it's the location of the found image.

What you want to do is use the found image condition of an If-Then action, like this:

image
image1306×748 97.1 KB

The If-Then is the controller, and it's acting on the found image condition. Does that help?

-rob.

7

Wow there are so many small things, that make an important difference! This worked, thank you so much!