Batch macro to filter countries and output to excel

[DanielCarro]

Hi guys,

Need some guidance on a batch macro, saw some articles over this subject but didn't work.

So I have data from one file and there are around 70 countries, where I just want to split the countries into individual excel output which I have a template.

Important: the template has formulas so the data needs to be inserted into a range A4:U2500

On my workflow it works well but I don't really want to be doing the filtering for 70 countries but I am struggling with the batch macro

Many thanks,

Dan

[MatthewO]

@DanielCarro the attached workflow demonstrates how this can be accomplished with the Output Data tool. By replacing the output path with data from the workflow, you are able to write data dynamically to different sheets, or files. 

[Kakuffo]

HI @DanielCarro 

The attached screenshot shows the configuration required for the output data tool to output to seperate sheets (Change file table name with take file/table name from field)  in excel or files in other format types.

[DanielCarro]

Hi MatthewO,

Nearly, your output creates tabs alongside. But what I needed is the country to go to the template and to sheet1 and paste the data in the range. So one country per excel file. 

Kind Regards,

Dan

[MatthewO]

@DanielCarro this can be accomplished with a small change to the file path created in the Formula tool. The attached version writes to different files.

[DanielCarro]

@MatthewO  it still creating tabs for the countries

[MatthewO]

@DanielCarro just confirming that you ran the workflow with the _v2 suffix. It generates 2 separate files when run on my end. The only change is that the value of the Output field is updated in the Formula. For use in your workflow, you will likely want to update "%temp%" with the appropriate directory. 

[DanielCarro]

@MatthewO  following your guidance

in the second pic I get a C inside a bracket

the last pic is the extension of pic 2, wasn't able to capture all in one shot

Not sure where I might gone wrong

[MatthewO]

@DanielCarro I think the issue is the configuration shown below. I would update this to "Change Entire File Path".

[Felipe_Ribeir0]

Hi @DanielCarro 

Please try this workflow. I shared this case in the gallery too, it is very useful i think: https://community.alteryx.com/t5/Community-Gallery/Copy-files-between-folders/ta-p/1056621

Just adapt it to your use case (the datasets/paths/range)

1-I have a default template

2-I am using a macro to copy this template for each country of my dataset

3-I am writting the data inside of each country/template