Assuming I understand your question correctly, you have a directory with multiple workbooks with different sheet names. For example:
And the names of these workbooks and sheets may change. I would use the directory tool in conjunction with a macro that iterates through these to grab the sheet names.
In the macro import a list of sheet names on an xlsx file with two controllers attached- one to replace the file path name, and the second to replace the '<List of Sheet Names>'. In the main workflow you can join records by position and you will have the full file path and sheet name for every file in the directory.
Hope that helps!