I don't have a license for Alteryx at the moment, so I'm not able to test this solution. That being said, I do believe the attached workflow will do what you need. In the previous version, the full path of the input file name should already be in the output. When reading from an Excel file, that full path should look something like: "C:\Temp\your_excel_file.xlsx|data". In this example, "data" is the name of the sheet. With this, we apply the following formula to dynamically parse off the sheet name from the full file path.
Tab Name = Substring([FileName], FindString([FileName], "|"), Length([FileName]) - FindString([FileName], "|"))
The formula above was added in the new formula tool shown in the red box below.
Hopefully this gives you what you need.