This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I'm not sure why I can't attach my workflow in this comment, but I tried your last idea and unfortunately it is still only saving the one sheet with the data and dropping all of the others. I must be doing something wrong here. Since I can't attach my workflow, here are some images:
In your first screenshot of the Output Data, do you have the "Take File/Table Name from Field" box checked at the bottom of the Output Data Tool configuration?
If not, you'll want to check that and then select the FileName field, and then select "change entire file path"
I can't see the bottom of that configuration, but my suspicion is that right now its overwriting the sheet every time, and only storing the last sheet because of the option selected in (3) in the "Options" pane of the configuration window.
@rachcamp7 based on the screenshots, it looks like your filename isn't actually dynamic. If you're wanting multiple sheets in the excel workbook, you'd need the sheet names to be different. In your case your filename formula is static with the sheet name as "[PCAT list]".
Assuming that's a field, then don't include [PCAT List] in quotes to ensure that it's leveraging the field name, not as a string. I'd also echo @tcroberts comment to ensure that the checkbox for "take table name from field" is checked in the output tool and using your field with the formula.
Also, as a side note, it's best to include the entire output file path in your output. Otherwise it outputs to a temp directory. You're only giving it a filename.