This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
You'd need to split the process into multiple steps.
Firstly, you will use the directory tool to load in details of all the files within the directory and use a filter to select the one with today's date.
Secondly, you will need to build out the full path to the file including the sheet names. If these are the same all the time, you can hard-code the logic however it'd be safer to use the approach you did
List all the sheets within the relevant work book
Also Full path to the file as part of the process
And then use another formula tool to replace the reference to <List of Sheet Names> in the full path with the individual sheet names
Replace([FileName], "<List of Sheet Names>", [Sheet Names])
Once ready, you can use another dynamic input to read each of the sheets within today's workbook.