Split out an excel workbook which has multiple sheets into separate files

Question

I have an excel workbook with multiple sheets within it - I'm looking to take each of these sheets and save them to separate .xlsx files. This is the input - each of the sheet names contains its own data in the workbook.

I use this formula to create the filepaths:

And these are the settings in the output tab:

This gives me the following output - however when I open these files, they only contain the file path, and not the actual data.

Is it possible to do what I'm looking for?

Jonathan-Sherman · Accepted Answer

Hi @srea541,

In this case you're reading in the sheet names (no data from the sheet - just the name) and writing out them into separate files. You'll want to use a batch macro to read in all the files (or if they're the same structure a wildcard union), select option 5 in your input tool "Output File Name as Field" as full path from where you can extract the sheet name with some parsing tools (Regex, Text to Columns).

Then you can use the part of your workflow to write out the data to seperate files (with the formula you created to make the file path including the sheet name as part of the file name)

Kind regards,

Jonathan