Create multiple excel sheets with two worksheets using one excel template

Hello all,

I have a master excel file with different department names - department 1, department 2, department 3, department 4, department 5 ............................. department 35

I have one excel template with 2 worksheets - worksheet A, worksheet B. Worksheet A is basically a list of people starting from cell B24, and Worksheet B has details (data dump).

The output I am looking for from Alteryx is to use this template to create 35 files with each department's data. For example Department A will have a list of employee's names in Worksheet A, starting from cell B24 and Worksheet B with the whole list of Department A.

Can it be done?

Thanks!

Workflow

Developer

Batch Macro

Accepted answers

csmith11

That's what this workflow does. It copies the template file to the output location. (4 times) Then writes dynamically to each copy.

Here I downgraded the workflow to 2019.3

Please let me know if you are able to unzip this folder and execute workflow successfully.

SaveAsDynamic.zip

All comments

DavidP

Hi @dsevilimedu, Yes, Alteryx can definitely do this. I f you can mock up some data, we can help you

csmith11

dsevilimedu

To get you started:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Dynamic-Output-Filename/td-p/544827

You'll want to use a dynamic file path that you can build with formula tools.

dsevilimedu

Hello,

Thanks for helping me!

Please see attached - input and output.

Input is basically data dump, has 4 departments

Output is the template with two sheets - the Summary worksheet is where I have formulas that connect to the Details worksheet. I want Alteryx to Output data, by department to the Details worksheet. Summary sheet automatically picks necessary data from Details.

So, there will be 4 files at the end. Each file will have two worksheets 1) Summary (where I have formulas) 2) Details (where I want Alteryx to dump data)

I am able to achieve 95%. I am just struggling to get Alteryx to output data, by department to "Details" worksheet.

If there is any other better way, I would love to learn that. But for now, I just want Alteryx to dump data in "Details" by department.

Sample Input.xlsx

Sample Output.xlsx

csmith11

Please note this workflow will create a BAT file that will copy your Template file to the output file location before writing to those copies. If you are building this workflow for use on Alteryx Gallery you'll need to use a Shared Drive and ensure your workflow will have permission to execute bat files on the server.

SaveAsDynamic.yxzp

dsevilimedu

Perhaps, a worflow that can cooy and paste templates a certain number of times and rename to department names?

Right now, I manually copy pasted my template 30 plus times, and used exce macro to rename them all. Then, I ran my workflow. It seems to work. But definitely a worflow that would copy, paste template would be awesome.

csmith11

That's what this workflow does. It copies the template file to the output location. (4 times) Then writes dynamically to each copy.

Here I downgraded the workflow to 2019.3

Please let me know if you are able to unzip this folder and execute workflow successfully.

SaveAsDynamic.zip

dsevilimedu

I finally figured it out and fixed it. It works like a charm! Thank you so much!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703