Write multiple excel files using a template

[oneillp111]

I am trying to write multiple excel files using a template.  My template has two pages.  One is a Form and the other is "Raw".  The Raw page is what I am trying to write to.  With the attached batch macro workflow, I am able to create the multiple files, however it does not keep the form page.  It creates the multiple files with only the "Raw" sheet.  Can anyone help me out with this?

[JosephSerpis · Accepted solution]

Hi @oneillp111 I've come across this problem I found one solution was to use the blob input tool to pull in the Excel template then use a blob output to push the template to the output folder where i'm writing me results from the output tools in Alteryx. You need to incorporate the blob tools as part of the workflow so that the template is pushed to the output location before the rest of the Alteryx workflow outputs the results. I've attached an example solution I made. 

[oneillp111]

Hi @JosephSerpis, this worked like a charm.  I will accept as solution, thank you.  One question that maybe you can help me with.  I am trying to use take file/table name from field in the output 

It looks like this "C:\Bot Files\Scrap\filename.xlsx|InputData" The InputData is a named range in the raw sheet.  Everytime I try to run it I get Sheet must exist error. But if I dont take the file name from field

it works fine, but only creates one file... any ideas?

[JosephSerpis]

Hi @oneillp111 my suggestions would be to change the entire filepath in the output options the rather than append suffix to file. I always build the filepath into the workflow so I can check it's going to the write location before I output. My other suggestions is I found it was easier to drop to a sheet rather than a named range. The last suggestion I have is I found creating and writing to a template in excel was always easier for an .xls rather than .xlsx

[oneillp111]

OK still running into an issue here.  It creates the all the files but doesn't keep the first sheet.  I am left with just a raw data file and the template sheet is gone.  

If I do not check the box below, it creates one file correctly with both sheets in the template

But when I apply the setting below, it creates the multiple files that I want but only has one sheet, does not keep the excel template sheet

I cannot figure out how to get both, multiple files and using the template.  Also I get an error when trying .xls files, seems like it is blocked or something.

Any other ideas?

[JosephSerpis]

It sounds like the template is being overwritten by the outputs. The blob tools will need to be part of the workflow so they run at the same time when you hit run and should be at the start of the workflow so that the template is pushed to the output location before your outputs write to that location. Do you have an example workflow you can share that possibly has dummy data? 

[ShravaniChanda]

Hey Joseph,

I am facing similar issue to the above query.

My case - I am having a single excel template file, this file contains multiple sheets first 2 sheets are having formats and formulas stuff. So i have used blob input and output tools to get multiple copies of templates based on my company codes list. 

there are 4 company codes A, B, C and D. at the end of blob output i have now A_template, B_template, C_template and D_tempalte files.

I want to output some data which are formatted to tables using reporting tool based on company code column. so there are 4 tables, A,B C and D tables these result should go to all the four templates based on company code with a sheet name check1.

I am not able to do that because render tool does not behave like output tool.  Appreciate your help .   

table configuration

1st layout config

2nd layout config

you can ignore join for now i tried getting path from blob tool so that i can create path, but it can help for output tool not render.

Please let me know your thoughts.