@rachelforbes
There's a couple of ways you can pull data from a Directory tool. What you have mentioned so far is correct that the you use the Directory tools to get the sheet names. The first way is you can use the Dynamic Input tool where you configure the tool to the location of the files and then use the field from your Formula tool to update the sheet names. That will pull in all the data from the directory. The downside of this method is that your schema must be the same for all files.
The second way is a batch macro. To do this, open a separate workflow, add an Input tool and configure the tool to one of the files. Then add a Control Parameter tool and connect it to the Input tools at the top of the tool. An Action tool will be applied in between the tool. Configure the Action tool to only update the sheet name. To do this you will remove all of the text besides the sheet name at the bottom of the Action tool. Here's the important part, if your files do not have the same schema, you can open the Interface Designer found in view click on the gear and change the macro to "ignore the schema". Then add an output tool and save as a macro. Sample attached.
