Dynamically change file location for multiple input workflows at once

[jnst238]

     Hello all! I am a new Alteryx-user creating an extensive workflow with dozens of inputs, all of which are coming from the same, very-large Excel workbook. However, next quarter, I will be using this same workflow but with a different, new Excel file. Is there is a way to change the file location for all of these input workflows at the same time, to the new Excel file, without having to manually adjust the file location in each individual workflow? 

Thank you!

[Robin_McIntosh]

So the Excel file structure will remain the same in terms of file and sheet names, or can the file name also change?  The only thing changing is the file location/path?

[jnst238]

That is a good question! The file structure will remain the same, but the sheet names that I am querying from will change slightly. To be specific, the only thing that will change in the tab name queries will be the quarter number, say, from 2 to 3. The exact cell ranges I am pulling from will stay the same now and in the future. Does this help?

[joelmiller66]

@jnst238  If you are having to update multiple Input tools with new file location, you can do it a few different ways.

One way would be to create an Analytic App to update all those Input Tools in one selection. This is assuming that all the sheet names will stay the same.

If the new file is going to be coming from the same location as the previous, you might be able to leverage the Directory Tool and create some dynamic logic to read them in.

[JalenBraegelmann]

The best option is to use a standard input tool, but configure it to read in a list of sheet names from the file instead of its contents. Once you get a list of sheet names, you can easily filter out the sheets that are not relevant. Once you get a list you will pass in each sheet name into a 'dynamic input' tool. Configure the dynamic input tool as a normal input tool first (it requires a base file as an example of format of the data). The only change is you will select the option to 'Change File/Table Name' using the Sheets list you already have.

When you run it will loop through each sheet being passed into the dynamic input. Feel free to attach a dataset and  I can build it out for you.

Standard Input: 

Dynamic Input: