Create multiple sheets on the same excel file with same template

Question

Hello Everyone,

After some searching on community I couldn't find something that can solve my problem.

I would like to generate an excel file based on a template. The problem is that I need to use same template for multiple sheets, but the sheets need to be generated based on a column from my database.

Example: Customer 1 Customer 2 Customer 3.

I will need to generate 1 file with 3 sheets that are using the same Template, and I would like not to generate every single sheets manually for the input template, because the sheets can be variable from run to run .

Thank you in advance!

Input Template.xlsx

OUTPUT.xlsx

markcurry · Answer

Hi @mihaicom20 , when you create the output sheets it typically won't have any formatting.  You can use the 'Perserve Formatting' feature, but to use this feature you need to update an existing sheet with the formatted already applied.  But in your example this is going to be more difficult, as you are dynamically creating the sheets.

It would be easier if you were creating multiple files with the same sheet name.  So in your case you could have 3 separate output files (Output - 1.xlsx, Output - 1 (2).xlsx., Output - 1 (3).xlsx.) .  I'm not sure if that's an option, if it was you could get the list of the different values in your 'No.' field, and then using the Run Command tool, copy the existing template with the formatting to each of the new file names.  Then after the files are copied with the formatting, you'd update the existing files.

It would be trickier doing this for a single file, with multiple sheets, there may possibly be a way of running some VB Script to copy sheets.  Either that or having a Excel macro run to reformat the Alteryx output.

More on the Preserve Formatting feature here.

Preserve Formatting and Write to Excel Range - Alteryx Community

mihaicom20 · Answer

@markcurry  Thank you for your help, but the Template attached by me has some formatting on some columns and you solution is not keeping them.

markcurry · Answer

Hi @mihaicom20

See the attached workflow, hopefully this should give you what you are after.   It uses the 'Take File/Table Name From Field' option at the bottom of the Output Data field.

The workflow uses the current path (.\) so you'll need to have the Input files in the same folder as the workflow for it to run, but you can change this to your actual folder.

Output Multiple Sheets.yxmd