Do all of the sheets in all of the files have exactly the same layout?
If yes, you can use a Directory tool that feeds into a Dynamic Input tool.
If not, you'll need a batch macro to bring in different layouts, then use a Union tool to combine the Sheets.
To get the source of data (file name and sheet name), whenever you use an Input Data tool, use the configuration option Output File Name as Field = Full Path
 
Check out this post:
The Ultimate Input Data Flowchart
Community > Designer > Designer Knowledge > The Ultimate Input Data Flowchart
https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/t...
And here are some posts about Dynamic Input, and reading multiple Excel files / sheets:
this solution requires all input Sheets to have the same layout
see response from LordNeilLord: Alteryx Certified Partner
I built you a little workflow to do this.....the first Macro reads all of the sheet names and the second macro imports those sheets into a single file
Directory.yxzp
Input multiple xlsx files and union them
Read multiple sheets from multiple Excel files
using a batch macro to import multiple sheets from excel
Community > Designer > Browse Knowledge > CS Macro Dev: Reading in Multiple Files with Different Field Schema - Batch Macro
How to Input Multiple Sheets from Multiple Excel Files
Chris
