Preventing an error when an input tab does not exist

[kkaplan1]

We have an Alteryx built off of one Excel with 42 different tabs. These tabs are all a bit different so we cannot use a Dynamic Input. The problem we are having is that if one of the tabs is missing in this Excel, the entire Excel crashes because it cannot find the specified file path. Is there a way to do some sort of an "if" statement on the input that lets that Alteryx know to continue with the other functions on the page and ignore that function if the tab does not exist? 

Thank you for your help in advance! 

[apathetichell]

do you take in a list of sheet names from this file? you should be able to see if one of the sheets isn't in the list of sheet name and prevent running that part of the workflow.

[kkaplan1]

I do take a list of sheet names. Each input is already set up as one of the sheet names but we want to be able to run it if only 2 of the sheets are actually present without having to change the inputs every time we go to run the Alteryx. How do we go about preventing it from running that part of the workflow? Thank you!

[NikyN · Accepted solution]

Hello @kkaplan1,

I would still highly recommend to use Dynamic Tool (but the attached macro), so you will be able to dynamically identified existing sheets and than load them into flow.

In case that some sheets have different columns/or should be passing different tool (and should not be loaded as single data stream), please use filter to choose needed sheets.

My recommendation is:

1) Directory Tool -- to load all files (or single file) that you want to load into flow

2) Formula Tool -- to add specific formula to list all existing sheets

3) Macro (attached) -- to read all sheets in specific file

4) Formula Tool -- to add to full path also sheet name from previous step

5) Filter Tool -- optional, in case that you want to treat every sheet differently

5) Macro (attached) -- to load data into flow

***Browse tool required for first run.

Best Luck!

Niky

[MichelleL]

@kkaplan1 Please see attached for a variety of options to bring multiple Excel files together into 1.  The solution is dependent on the set up of the Excel files (same schema, different schema, same sheet names, different sheet names, sheet sequence number, etc). You mention "These tabs are all a bit different so we cannot use a Dynamic Input".  Why?  How are they different and how are you bringing them together?  If you could send a pic of the workflow, that may help me understand.

Hopefully one of the attached samples will help you out.  Let me know if one of these works for you!

[Peachyco]

You might consider the Tool Container. It has an Enable/Disable switch on it, so if you put something like a "subroutine" in it, you can effectively prevent that portion of the workflow from running altogether based on some rules that you set.

Maybe you can follow from this particular discussion to see how you might be able to pull this off based on conditions.

[kkaplan1]

Thank you very much for this!

[DylanDowrick]

In addition to the answers provided, there is a formula:

IF FileExists([FullPath])
THEN [FullPath]
ELSE ""
ENDIF

which can be directly before the input anchor of a macro/dynamic input.