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
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
@jsilavong hi, you can create the path for file to write (in separate column of your dataset) and then use it in output tool:
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 :-)!
Thanks @Sebastiaandb
When I download your workflow I only get the below... am I missing something?
@jsilavong you can do this by using dynamic input, creating new filepaths and then outputting. Please see attached.
Essentially:
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.
@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.
You are the BEST!!!! Thank you so much for going above and beyond to help me. Have a great day!
@jsilavong
No problem! Awesome reply, just made my day!
Morning @Sebastiaandb,
I have another question if you do not mind assisting with. I was able to use your workflow to split an excel file into multiple files via its sheets. Now I need to find a way to set a password on each of those outputted files (password protected excel files).
Would you be able to assist me with this? I believe I may need to put a batch file in the macro which looks at a table to assign each workbook a password. (Each file needs to have a different password depending on sheet/file name).
Any guidance or assistance would be greatly appreciated !
