Output multiple Excel files with multiple ranges using one template

Question

I have two output tools - one is writing to range A1:F2 on Sheet1 in an Excel file, and the other is writing to range A5:G100 on Sheet1 in the same Excel file.  I can do that when I'm writing one file containing all customers. However, I need this output to be split by customer and write a different Excel file for each. I imagine the answer is going to involve a macro? Does anyone have any suggestions? Thanks!

apathetichell · Answer

you need to create a dynamic path variable and use that with replace entire path. This does not require a macro (depending upon the situation) but a batch macro is usually useful everywhere. always. all of the time. sometimes multiple batch macros feeding into each other.

but I digress...

anyway - your path should be of the syntax "yourfilename"+[grouping variable]+".xlsx|||sheet1$A1:F2"

and

"yourfilename"+[grouping variable]+".xlsx|||sheet1$A5:G100"

there are some specifics here involving "`" if your sheetname has special characters like spaces...  in the output tool you need to set the options of the bottom of the output data field to change entire path... and IMPORTANT - you have to make sure you are sending in only the correct amount of fields for that range - so click on deselect your filename variable

DataNath · Answer

Definitely sounds like this is a case for a batch macro, grouping on customer when you come to configure your control parameter! Some useful links:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Getting-Started-with-Batch-Macros/ta-p/488165

https://www.youtube.com/watch?v=NNH_K5U5r78