Output multiple files using the same excel template

[Tienemientje]

Hi,

I often need to create +-30 files for +-30 different countries based on the country in one of the columns.

I have already figured out how I can divide my data over different excel files and tabs.

However, I like to start from a sort of template where I have some instruction text on top and some formatting. 

Whenever I need to change that template, I copy the new one 30 times and change the country name, so I can overwrite part of the file later with Alteryx data.  This works, but it takes some time and manual effort.

Is there an easier way to copy that 1 template 30 times with the correct output name?

example:

country Belgium_data.xls

country Germany_data.xls

...

Second question: is there a way to have some formatting done by Alteryx, like column width, cell color,...?  On the community I found some answers about table-layout-render to xls.  But I cannot seem to get that working with my different output files.

[DavidP]

This might get you going. The reporting tools take a bit of getting used to, but once you get the hang of them, you can actually do quite a lot with it.

There's a lot of formatting you can do with the report tools.

[Tienemientje]

A very big thank you.  This is indeed a big help!  I can start exploring that part now.  As I first thought it was not possible to divide it over different files, I did not look into it yet.

I do want to keep it open in case someone else knows the answer to my first question if it is possible to have 1 excel template and copy it 30 times with a different name (based on a list).  So it can be used later to be partly overwritten on the different tabs.

I know how to place the data on a certain predefined date range on different tabs, only if I need to adapt that start file, I need to do it for every country separately at the moment.

Kind regards, Tienemientje

[DavidP]

You can do this by writing a batch macro that uses your list of countries as the control parameter and the macro loads your template file, writes the data to the desired ranges and writes the output file with the country in the filename.

I'll try and mock up an example for you.

[Tienemientje]

Thank you.  Maybe this might help?

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Output-multiple-files-using-the-same-excel-t...

There is already a topic on it, but as a newby it is quite difficult to understand.

So basically, what I need is:

start from 1 excel file (with multiple tabs and formatting), called "template" (or anything else).  And save that 30 times, but called: template_belgium, template_germany,...

And those country names I can put in a list/table like you did in the previous post.

[DavidP · Accepted solution]

ok, so attached is an example of a batch macro and I'll talk you through it.

Forget about the macro bit for now, your 1st objective is to write a workflow to generate a dos command that will copy your template file to another file with a new name. You can make it so that it writes it to a different folder if you want.

I used a text input tool to create 3 fields

I then used a formula tool to build my dos command. Note that you have to be able to copy the output from this command to a dos window and execute it.

The formula:  'copy /Y "' + [Temp Path] + [Template] + '" "' + [Temp Path] + [Country] + ' ' + [Template] + '"'

gave me a dos command like this:

copy /Y "C:\Users\davidp\Desktop\temp\template.xlsx" "C:\Users\davidp\Desktop\temp\UK template.xlsx"

The double quotes are needed if there are spaces in your filenames.

You can now pass this to a Run Batch Command tool to execute.

The next step is to turn it into a macro so that you can update the value of "Country" to create files for each country and the folder location where the template is. That's what the Control parameter and Action tools do.

The final step is to call your macro from a workflow and feed it a list of country names.

I'm including the macro, the workflow and my template.xlsx file

My workflow is expecting the macro in C:\ProgramData\Alteryx\Macros. If you put it somewhere else, you just have to remove the macro from the workflow and re-import it from your macros tool palette.

I've given the Macro 2 control parameters: Country and Folder.  You'll have to create a folder (mine was C:\Users\davidp\Desktop\temp\) and drop the template file in it. Then you'll have to change it in the workflow text input tool.

Let me know how you get on.

[DavidP]

sorry, wrong pic. This is the right one

[Tienemientje]

Thank you so much @DavidP, it works great.

This will save me a lot of time.  

Kind regards, Tienemientje

[kavinganesh]

Hi DavidP

If need to copy certain template sheet in the excel WB what should be the formula or how to add template sheet name while specifying the template.

Thanks in advance

[DavidP]

Hi @kavinganesh,

It sounds like yours is a slightly different problem. The suggestion above copies an entire Excel template file (not just one sheet) with a DOS command to a new location with a new name and can then write data to the new file.

If you can you explain your specific problem with a bit more detail, I'll hopefully be able to help you.