Stuck on how to import sheet name

[RCern]

I have a folder on a network drive that contains 21 .XLSX files that each contain several sheets of data.   My goal is to extract all data from each sheet and from each file and combine into one. 

I am using the Directory Input tool and two batch macros to try and accomplish this. I can't upload the workflow due to security data, but I attached screenshots.

The first screenshot is the overview of the workflow so far.  I have the directory input tool that feeds the first batch macro to identify all sheet names in each file in the folder.

 

The second screenshot is the batch macro that attaches the sheet name to the file name for each file in the folder.

 

The third screenshot is the second batch macro that takes the filename+sheetname from the first batch macro to identify which files and sheets to pull the data from and combine.

Again, I am trying to now add a new column to the combined data that identifies the file name and sheet that each row of data came from but I can't seem to think my way through it.  If you know of an easier way to pull all sheets and data from multiple .xlsx files and combine them into one with an identifier stating where that row of data came from, I am complete open to suggestions lol.  Thanks in advance.

[binuacs · Accepted solution]

@RCern in your second macro select the Full path option which will output the full path of the file +sheet name, from this field you can extract the file name and the sheet name using a regex formula

[RCern]

@binuacs OMG so easy... Now I look foolish lol.  Thank you!!!