Passing formula to dynamic input without sheet name

Question

Hi,

I am using the formula tool to pass a string to the dynamic input tool e.g.

"C:\User\MyDocuments\" + TimeDateFormat([DateOut]],%yy%mm%dd) + "\TargetFile_*"

Using * at the end as the target file has a timestamp that includes hr min sec which I won't know but the "TargetFile_" prefix will stay the same. TargetFile will be an excel .xlsx

The problem I'm running into is when parsing this into a dynamic input tool is that it says 'no sheet specified'.

- I can't 'replace entire file path' as adding "|||TargetSheet" after the * doesn't seem to work, but I need to keep the * because I won't know the hr min sec timestamp in the file name

- Change File/Table Name doesn't seem to work as it looks for the entire string as if it's a sheet/table name

Is there no way to amend the file path but keep the sheet name from the input template specified in the dynamic input tool?

Thanks in advance for any guidance you can provide

mceleavey · Accepted Answer

Hi @ah13303 ,

I've built a macro (see attached) that retrieves all sheet names for every Excel file in a given directory, you can then append the sheet name to the fullpath using a formula.

Use the directory tool and input the "fullpath" field into the macro on the "Questions" tab.

hope this helps,

M.

ah13303 · Accepted Answer

Thank you both for such quick replies, very impressed with the community spirit! My issue was a lot more simple than I thought and I found the answer here.

Updating my formula with a $ sign after the target sheet seemed to correct the issue for me.

"C:\User\MyDocuments\" + TimeDateFormat([DateOut]],%yy%mm%dd) + "\TargetFile_*|||TargetSheet$"

Weird as it was not a problem for all files I was pulling sheets in from but I shan't look a gift horse in the mouth. Thanks again!