How to read from different Excel files all with Sheet1

Question

I have a macro that figures out what last month is and puts it in the format mmmyy, i.e. Jul19.    I have Excel files placed in a directory names Rate-mmmyy.xlsx.  i.e. Rates-Jun19.xlsx, Rates-Jul19.xlsx, Rates-Aug19.xlsx.  The only sheet in each Excel is Sheet1.

In the past I have used the dynamic input tool to read in Alteryx Database based upon the value from the macro.  So, I'd read in Alteryx-Jun19.yxdb or Alteryx-Jul19.yxdb depending on the output from the macro.

I'm trying to do the same with the Excel files.  Only now, the dynamic input tool wants to change the table, or sheet, it is looking for to the value in the macro. It won't change the filename it is looking for.

Is there another tool to use, or a specific way to configure the dynamic input tool that I'm missing?

afinzerillo · Accepted Answer

Thanks for the reply.

I looked at the documentation you referenced and I'm not sure it helps.  I want to read one file, not many.

My macro determines the month as variable of Period.  So, I want to read in file Rates-[Period][.xlsx.  This month it is Rates-Jul19.xlsx.  Next month it is Rates-Aug19.xlsx.  Both of them have Sheet1.

The Dynamic Input tool wants to take the variable Period and look for a sheet named Jul19 instead of looking for a file ending with Jul19.  It works when reading in files that aren't Excel.

Not sure if I'm configuring the tool incorrectly or if there is a different tool to use.