Catch and log which Excel sheets were not found

[okka]

Hello everyone,

I'm relatively new to Alteryx. I've made a macro that is reading a list of Excel files and two of their sheet names (called "Main" and "New Trades").

The macro works well, however, sometimes a few files are missing one of the two sheets. Thus, it outputs errors stating "Record #1: Tool #2: `New Trades$` does not match a sheet or named range in [FILE]. blabla...."

What I would like to do is to "flag" which files are missing which sheets (kind of logging them into a list). Is there any tool or any way to do this?

Also, I don't want my workflow to stop working because it couldn't find a certain sheet in one file (what would be great is to just output one warning or something)

Many thanks

[BrandonB]

Could you isolate the file path/sheet names from what you are reading in and join them to a list of sheet names that you expect? You could get your list of files and append on the sheet names you expect, then join that against the actual list. You could then see the L or R from the join tool for files that do not have the expected sheets in them. 

[okka]

I guess this is what my macro is doing already (attached is my macro)?

My inputs are the filename and the sheetnames.

[BrandonB]

Correct, but I am referring to outside of the macro. Take a look at the attached workflow:

[okka]

Thank you for your answer. However, here you are assuming that I already have a list of file names and their respective sheet names.

This is not what I have now. I just have a list of file names that I'm reading from (and I'm assuming that the two sheet names are always in each one).

[neilgallen · Accepted solution]

Given that you're working with excel files, you have an opportunity here because Alteryx can read a list of sheet names from an excel file.

If you use a directory read tool you can then read the available sheets from each file, and then only input those sheets into a dynamic input tool, or whatever you'd like.

if you're assuming that each file should have at least those two sheets then you could also flag those files that do not, as well.

The attached workflow should get you started with the rough idea.