Run the same workflow for multiple files & output to different files

Question

Hi

I am looking to run the same workflow (500 times) currently I have this automated to enable me to pick the file out of a file path add the sheet on and then run for say 5-6 sheets. save the output down and then re run for the next lets say 'customer'. this would mean running the workflow 500 times and copying the output etc... would take 4-5 hours

Ideally i'd drop all the files (as shown file 1/2) in a filepath. & have a macro run the workflow for all sites and output the output based on the customer name.

Help required on the batch macro running for each file? Id assume based on full path or some form just need help with the set up.

Output the file based on customer name?

Thanks Dan

Help community.yxzp

FinnCharlton · Answer

@danjohnsonM Ok makes sense, here's how you can dynamically output one workbook per customer name:

We dynamically form a file name using the customer name, and then use the 'Take File Name From Field' option in the output tool to dynamically output to multiple files. Hope this helps!

Dynamic Input.yxzp

danjohnsonM · Answer

Thanks - my workflow already does this as I just have a seperate input for each tab

I more need the help on creating this as a macro and having it output to the workbook based on customer name

FinnCharlton · Answer

Hi @danjohnsonM , here is one way to do this using the Dynamic Input tool:

* Directory tool lists all the files in the target folder.
* First Dynamic Input lists every sheet name for all the files in the target folder.
* Formula updates the file names with the sheet names. We now have a file path for every sheet we want to read in.
* Second Dynamic Input reads all of these sheets in automatically and unions them together. Note - this will only work if the schema does not change.
* After this I've just tried to recreate your analysis, you can add anything here.
* Workflow attached - hope it helps!

Dynamic Input.yxzp