Output multiple files using the same excel template

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.

Output

Accepted answers

DavidP

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

text input.png

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.

run batch macro.png

create multiple templates.yxmd

Run batch command batch macro.yxmc

template.xlsx

All comments

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.

multiple excel files based on country.png

multiple excel files based on country.yxmd

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703