Dynamic Input - Excel Table/Query

[Mcurr]

Hello!  I am looking for a way to dynamically input Excel files that require a selection of a specific tab on the file.   The issue I am running into is the tab that I need has the year as part of the label.  This of course will change each year.   How can I dynamically select this tab from the file.  I am just learning about wild cards in the file path which is what I intended to use to capture the year update in the file name, but I cannot figure out this Table/Query. 

 

[perin]

Hello @Mcurr 

You can use Macros to support you in that case, you can read a list of files through directory tool and after that, use a macro with an input tool in order to list all the sheet names in all files. after that you can filter what you want and use the dynamic input or another macro to read all the files.

Best,

Gustavo.

[griffinwelsh · Accepted solution]

@Mcurr you can feed the filepath + sheetname that you need into a dynamic input tool to accomplish this. Use the datetimenow() function to get the current year.

[Mcurr · Accepted solution]

Thank you for this @griffinwelsh! 

I ended up reading in a list of the sheet names, adding the names to the path, filtering for what I will need going forward then feed that into a Dynamic Input.  This appears to be working for what I need.  I think I will run into issues if I use the datetimenow() function as I could be running this in the new year for the prior year end.