Dynamically select a sheet after directory input

I am trying to figure out a way to dynamically select a sheet that changes name each time the file is provided. I have a file that is provided on a more or less weekly basis which has a sheet name that includes a partial date in the format of: Raw Data Till 19 Jan. The portion after "Raw Data Till" changes based on the day that the file is provided, and since I don't want to have to manually select a new file and sheet each time a new file is provided, I am looking for a way to dynamically grab that sheet. Right now I have the inputs coming in from a directory, but I am not opposed to using a wildcard to get the right file and sheet if that is needed.

Input

Tips and Tricks

Help

Accepted answers

MattR79

Unfortunately, the file is not sent daily and can be used for up to a week after receipt to run workflows, so the datetimenow() function will not work. I did figure out a way to make it work as shown below, and while it is not the most streamlined way it does seem to be doing what I need it to do.

All comments

jrlindem

Each time the new data is made available.... is it a whole new file? Or is it a new sheet within the same file?

My mind goes to Directory Tool + Dynamic Import, but that also depends on the schema being fairly stable. Batch Macro is another option. You could also use the input tool with a wildcard filename format and then "query the sheets" within that file and then bring them in. Of course, only those sheets that match the format "Raw Data Till..."

Any of that resonate? -Jay

dreldrel

Hey @MattR79, you can use the Dynamic Input tool to achieve this. Dynamic Input is a powerful tool when your file names or sheets are not static. Here’s a sample showing how to pick up a dynamic sheet name, assuming the file is sent daily. You can update the file path/file name/sheet name as needed

[FullPath] + "|||Raw Data Till " + DateTimeFormat(DateTimeNow(), "%d %b") + "$"

MattR79

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703