Create multiple excel sheets with two worksheets using one excel template

[dsevilimedu]

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!

[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.   

[csmith11]

Use can use this formula if you want the files to be saved relative to where ever your workflow is saved:

[Engine.WorkflowDirectory]+[COL 13]+".xlsx|||details"

If you want to save it somewhere else, just remember to specify the entire path including the sheet name. Use the "|||" to seperate the sheet name from the file name.

Notice the bottom left has settings to update the entire path. So the Top Left file name will not matter. (I used place holder with a place holder sheet name). To the right of that, you'll want to uncheck the box that says Keep Field in Output. This prevents the Full file path from being included in your output file.

See the 4 separate output files created below. ( My understanding is that the Summary sheet will already exist in the Template file correct? Or is this a sheet that you will be creating as well? If that's the case you'll likely be needing to make a copy of your template file before writing to it. I'll put that together and share an example shortly.

[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.

[dsevilimedu]

Thanks for the workflow @csmith11 .

What this does is create individual department file, and not adding to the template (Source Output) -> summary sheet. 

Ideally, there should be 4 department files with two worksheets. Is it possible to achieve that? 

[csmith11]

Did you already try the second example I sent? It maintains the Summary sheet form Template Files while writing data to the Detail sheet. The Formulas should then pick up the data as expected.

Are you not seeing the output files with 2 sheets? Did you use the .yxzp I sent you?

Mind sharing a screenshot of your message log and output files?

 Message Log

See example image of the output file I receive when ran on my end.

[dsevilimedu]

Ahh, I see

I tried and got the following error (I am sure I am doing something wrong :()

"Failure to Import C:\Users\dsevilimedu\Downloads\SaveAsDynamic (6).yxzp.
There was an error opening "C:\Users\dsevilimedu\Downloads\SaveAsDynamic (6)\SaveAsDynamic.yxmd":

This workflow was created by a more recent version of Alteryx, and may contain tools or functionality not present in this version. Alteryx does not support using an earlier version of Alteryx to open a workflow created with a newer version. For best results, download the latest version of Alteryx.

Do you still want to attempt to open the workflow?"

How do I upgrade?

[csmith11]

If you tell is yes, does it successfully import?

No new tools were used. So you should be fine. What version of Alteryx are you on?