Happy 8th birthday to the Maveryx Community! Take a walk down memory lane in our birthday blog, and don't miss out on the awesome birthday present that all Maveryx Community members get to take advantage of!
If you are on v10, then the way to do this would be to read the directory in using the directory tool and then feed that into a dynamic Input tool, with <List of Sheet Names> selected as the sheet on the template in the Dynamic Input tool. That will return a list of sheets for each file, which you can then use a formula tool to make the filename|Sheetname combo you need. So in the Dynamic Input, your settings for the template will look like this...
Use a Directory Tool and make sure the configuration is set as per below where file specification is your file type and you select the sub directory checkbox to include the files from your sub folder as per your requirement.
Then select the dynamic input tool and make sure the configuration is as per below,
This will work if there is one sheet in each of your excel files.
If there are multiple sheets in your excel file then you will get an error on your dynamic input tool which will say "No sheet specified"