Make copies of Excel template sheet in Alteryx

Question

I have an excel file with a template sheet with some pre-populated fields and some placeholders. For each run, I might be expected to create 5 output sheets in one excel file. Each of those sheets will be a copy of the template with some additional data inserted in the placeholders. For instance, if there are 5 countries, I will be asked to create the same templatized output with data from the 5 respective countries. So, the output would have one excel file with 5 different country-specific data. Is there a way to achieve this in Alteryx? Can I use a Run command file to make copies of the input template?

Thanks in advance!

danilang · Answer

Hi @Paulomi

To make copies of the template sheet multiple times, you'll need to drop down into the VBS world and call macro that's in the template file.  An overview of your steps will be

0. build a vbs script template that creates multiple copies of the sheet using a FOR loop to copy the sheet X times.  (You only need to do this once)

In the workflow

1. Read in your input data

2. Determine the number of copies to make

3. Copy the template to its final location using the Run Command or Blob method

4. Read in the vbs script.  Read it in as a csv file with \0 as the delimiter.

5. Modify the script

a. Change the file path to the path from 3

b. Change the upper loop bound to be the number of copies from 2

c. Save the script

6. Run the script using a method similar to this

7. Write your data to the various sheets in the new file

Dan

Paulomi · Answer

Hi @danilang Thank you for this! But, my problem is a little different. It would be easier to copy the file with the template sheet and output various datasets to it. But what I need is to take the template sheet and make copies of it within the same excel file. So the excel file with the original template file would eventually be the output with 5 different sheets (Derived from the same template sheet). Any ideas?

danilang · Answer

hi @Paulomi

Check out the solution here using the Run command and another method here that uses the Blob tools to copy the template files before writing to the copies

Dan