Splitting Excel File to multiple Outputs

[jsilavong]

Hi, 

I am looking to have an excel file with multiple sheets to be split into separate files for each sheet. If someone could provide a test workflow that I could adjust would be awesome. 

Thanks, 

Jon

[JarekSkudrzyk]

@jsilavong hi, you can create the path for file to write (in separate column of your dataset) and then use it in output tool:

[Sebastiaandb]

Hi @jsilavong 

Here you go, this should work for you :-):

You basically feed the sheetnames to the batch macro that batches these sheets and outputs them to separate files :-). 

Just make sure to change the paths and you're set to go :-)!

Greetings,

Seb

[jsilavong]

Thanks @Sebastiaandb 

When I download your workflow I only get the below... am I missing something?

[alisonpitt]

@jsilavong you can do this by using dynamic input, creating new filepaths and then outputting. Please see attached.

Essentially:

• Use input tool to get a list of sheet names
• Feed that into Dynamic Input using Field: Sheet Names
• Parse out the tab name and build a filepath
• Clean up :)
• Output to multiple files using the filepath you built

[Sebastiaandb]

Hi @jsilavong 

I added a new version that has relative paths so as long as you keep the TEST file in the same directory as the workflow/macro, you'll be fine (see attached)!

If you want to alter the old version, this is the way to do that:

You have to do a couple of steps to make it work in your environment :-):

1) Change the path of the input tool to your local/network path:

2) Right click the  macro and click open macro, then change the path for the input tool again:

3) In the macro change the path in the formula tool to your own local drive/network drive:

4) Just to make sure, do the same for the output data tool:

5) Just to make sure, check both action tools to see if the path is updated and still does the right thing:

Then press ctrl+s to save the macro and run the workflow again. It should work, the errors it dropped at your machine were purely because of the workflow/macro referring to my machine instead of yours. 

[jsilavong]

@Sebastiaandb 

Thank you for clarifying. I was able to use the workflow successfully after adjusting the paths. My output from the test file though is not what I expected. Its combining all the sheet data from my input into one output file. I wanted to divide the input excel file by sheet into multiple files with only the sheet data on each. 

[Sebastiaandb]

mmm @jsilavong, that's weird. For me it's spitting out multiple Excel's based on every sheet you have in your source like:

[jsilavong]

@Sebastiaandb 

Do you see anything wrong with my attached?

[Sebastiaandb · Accepted solution]

Hi @jsilavong ,

Yeah i found it!

Open the macro and see:

It wants to replace "BOO" in the formula tool, that was the dummy i gave to the name of the Exceloutput to be replaced by a different name.

To work you need "BOO" in the formula tool as well like:

I'm sure it will work afterwards :-).

Greetings,

Seb