Output to template, on multiple sheets, with multiple filenames based on grouping

[jeff_ard]

Greetings Alteryx Family! Hoping you can help here!

Context: I have a large list of items that I manually create groupings out of. For each Grouping I must populate a 2 sheet template with information on the list of items (at 2 different levels of detail - hence the 2 sheets). These are customer facing templates and therefore must be formatted and look nice (they cannot just be data dumps), and 2 separate files is not acceptable.

I attached a sample flow and sample output template that is a representation of my data and output requirements. This is about as far as I can get on my own - I can successfully populate a template ONCE as well - but iterating through populating the same template for multiple groupings, and iterating through populating 2 data streams on 2 different sheets in the same file is beyond me! I know this will likely be some macro work, but not sure where to start.

HELP NEEDED
The simple sample flow attached results in 2 separate, unformatted outputs for each grouping - this is not desired.
-I need this to populate a a formatted excel TEMPLATE (not an unformatted output). I cannot figure out how to do this AND repeat for each grouping - saving to a new filename each time.
-I need the Identifer Data and Detail data on TWO sheets (2 templates) in the same excel workbook - not 2 separate files. I cannot figure out how to do this AND repeat for each grouping, while still using the templates
- I would like some information (the grouping) to populate as a header above the table (rather than just part of the filename). Help on the first 2 may answer how to accomplish this one.

Thank you!

[DavidP · Accepted solution]

Hi @jeff_ard,

Attached is an example of what you asked for. 

I've had to amend your template file slightly to make it work. You'll have to save the attached SAMPLE Template.xlsx file in a local folder and then update the Folder field in the Text Input tool where the folder path is defined.

The workflow creates copies of the template file based on the number of groups in the data. This is done with a DOS Copy command using a Run Batch Command tool.

The various bits of data is then written to specific locations in those files using predefined ranges in the template file.

Have play around to see if it works for you and feel free to let me know if you need more help.

[jeff_ard]

Amazing work! Gave me some more tools to work with - and appears to be doing everything I asked for! 

[Prateek_Gupta25]

Hi @DavidP  - Thanks for sharing the solution. Do you mind sharing the CMD code as well. 

I'm also stuck at a similar problem and exploring options. 

Also, I'm using 2019.2 version, please confirm if this will work in this version - my guess is yes but please confirm. 

[DavidP]

Hi @Prateek_Gupta25, the Cmd code gets generated in the Formula tool after the Unique tool in the branch leading to the Run Command tool. The run command tool then saves it as batch file in a temp folder and executes it.

In this case the code is: 'copy "'+[folder]+[Template filename]+'" "'+[folder]+[filename]+'"' where the individual fields are generated earlier in the workflow.

It should work fine in 2019.2 - just open the workflow file in a text editor like notepad and change the version number in the 2nd line to 2019.2

[MichelleColl]

Hi David,

I'm hoping to use a version of your workflow to automate a chunk of work for my team. I understand the concept but need to change the output grouping. I need the information grouped by customer, and output as one populated template per customer. Could you explain how I would change 'write data to identifier data sheets in all group files" section to match what I'm needing? Sorry if I've explained it poorly. Thank you in advance for any help you can provide. I really appreciate it.

[DavidP]

Hi @MichelleColl,

I'd be happy to help. Do you can come up with a dummy dataset that contains a few lines for each of a few customers and an example output file for one customer to use as a template?