Excel file as an input - File name remains same but tab name changes daily

[Idyllic_Data_Geek]

Hello,

I'm connection to an excel file for input. The excel file refreshes everyday. The file name remains same but the tab names changes daily with the new date. I used the directory and dynamic input tool where I configured it to replace the full path. This approach did not work for me,. Can you please help me in setting this connection so that I don't have to update it daily.

Thanks in advance

[binuacs]

@Idyllic_Data_Geek one option first extract your tab name using input tool then with the help of a batch macro you can combine all the tab. 

[Idyllic_Data_Geek]

There is only one tab in the file. For example todays tab name was refreshed to 'ABC For 13JUL2022'. for yesterday it was 'ABC For 12JUL2022'

[binuacs]

@Idyllic_Data_Geek for one tab also it should work

[Idyllic_Data_Geek]

It should work but is it the best and easiest solution?

[Emmanuel_G]

Hi @Idyllic_Data_Geek ,

Find a test below. 

When you import your excel file, you can ask to Alteryx to output names of sheets.

With the formula tool you update the current sheet name and with dynamic input, you read the current sheet of excel file.

Find this test below.

[Idyllic_Data_Geek]

should it be change entire file path or change file/table name? 

[Emmanuel_G]

@Idyllic_Data_Geek 

Entire File Path to read to choose.

In case you have many sheets, change file/table name.

But in your case, if I understood correctly, It's good with "Entire File Path to read" .

[Emmanuel_G]

@Idyllic_Data_Geek 

To be more clear about dynamic input , the file you provide in Dynamic Input is just a template. 

Dynamic Input knows that the FilePath in his input anchor is an excel file and since the template file is an excel, it will interpret and read the anchor input file as an excel (even if the sheet names are different between the anchor input file path and the one inside the dynamic input).

In output, you will have the data of the current sheet of your file and not that of the dynamic input.

That's also why you select "Entire file path" because it will read the file as an excel (because the template is an excel) but it will for the new file path.

[Idyllic_Data_Geek]

@Emmanuel_G  What is the formula tool doing? I'm unable to make it work? Are we replacing the file name or the sheet name? File name remains the same in my problem statement. It is the Sheet name within the file that is changing.