How to output to multiple Excel files using one template?

SkalTax

Hello Pros, 

I am hitting a wall trying to figure this out and I am reaching out as a last resort. 

Inputs

File1 - Excel file contains year-to-date amounts and includes many data fields (31). One field contains company codes and the file is grouped (sorted) according to company codes. 

File2 - Excel template with 3 tabs. First two tabs are not to be changed and have formulas linked to the third tab called 'Alteryx Output'.

Outputs Required

Create a separate file for each company code. Each file should contain, in the 'Alteryx Output' tab, the data from File1 for just that company.

Below is the workflow I have thus far and the parameters I have selected for the Output tool. The workflow inputs File1, filters, selects relevant fields and adds a field for the new file name/path.

I am able to generate files for each company, however, the two standard tabs from File2 are not present. For some reason, they are not brought into each output file or are deleted? I've found discussions using Blob input/output and Batch Macro but I was unable to replicate it successfully. I think a Batch Macro may work but not sure if it is an overly sophisticated solution for this issue.

Thank you in advance for your help. 

Skal

jrlindem

In this case, which is going to be a bit hard to describe, I like to:

1. Use the Formula Tool to designate a field that will spit the data into however many parts
2. Use the Formula Tool to create a Batch Command that will take your template file and copy/paste it X-number of times and name it according to each partition you want your data to be split into (from step 1).  You're creating the copy of the existing template and the paste into the directory where you want the deliverables to land.
3. Use the Run Command to execute the copy/paste Batch Command for each template copy
4. Output your data using the Output Tool, populating the corresponding records into each of the new template copies (that are renamed to match) and only into your third tab, respectively.  You do this overriding the output filepath and file name in full (with sheet name too)

I hope this makes sense.  What you need is possible.  I would read up on using Run Command Tool to help you accomplish the template copies and then look up outputting using "Batch Reporting" method.

Hope that helps, -Jay