Dynamic Input Tool to List Sheet Names - Error I must select a sheet?

[dixoncat812]

Any help would be greatly appreciated. I have this on other workflows and even in the workflow I'm using but getting this error on one file input. 

I have set my directly tool. I have all input files in this directory so I have filtered for the file name as "contains" xxx as the files always start with xxx and the date they were run. The filter tool output is the file for the file I'm looking for. At this point I'm good. The file typically contains multiple sheets. I've added the Dynamic Input tool here, selected the path, selected list of sheet Names. They show in the preview, but when I run the workflow regardless of what options I select in the read a list of data sources I get errors that I must choose a sheet or that the sheet does not exist. 

the file is not corrupted, I can open it. It works if I select the file directly and do not use the directory and dynamic input, but I want to schedule so I need this in the future. 

What am I doing wrong (as it works with all the other inputs) or what can I do differently to avoid this?

[Yoshiro_Fujimori]

Hi @dixoncat812 ,

Can you show us the content of [FullPath] you prepared?

(Typically the full path for excel sheet should be passed to Dynamic Input toos in a format like [FilePath] + "|||" + [Sheet Name]. So I want to check it.)

Also please share the error message you got.

[Qiu]

@dixoncat812 
Maybe I just dont know how, but I always failed to use the combination of Directory + Dynamic Input to get all the Sheet Information for multiple Excel files.

I will rather use the Data Input tool with Wild card as below.

[Calvin6KV · Accepted solution]

Hi @dixoncat812, I faced similar issues when building my own workflows. One solution that I used is to first filter for the required files from the Directory tool and then use a batch macro to extract all the sheet names (note: select "FullPath" as control parameter for the macro). I have attached my macro below. Do let me know if it helps.

Sample output:

[dixoncat812]

Thank you to all to replied, I have a few options that work. Calvin6KV, yours was the one that made the most sense to me so that is what I'm using.

Very much appreciated!.