Output to multiple Excel files or sheets

[#1]

This macro allows users to easily output their workflow to multiple Excel files or sheets, removing the need for creating a custom file path each time either of these options are required.

The configuration is very intuitive and allows the user to:

• Select a target directory
• Choose whether they want multiple files or sheets
• Input a filename if multiple sheets is selected
• Nominate a 'grouping' field which the data set will be split on

As an addition, the macro also removes 'illegal' characters that cause issues when saving .xlsx files, such as #!%.

This is a fairly basic macro built to service a request I noticed coming up very frequently within the Designer discussion forum. In the future I will look to add more functionality. Any feedback/criticism/requests are very much welcomed!

[Beckyli]

your workflow is too complicate, i just need simple correction.

[DataNath]

@Beckyli you don’t copy in the workflow itself here. On your main workflow you right-click

on the canvas > insert > macro and navigate to wherever you saved it. You’ll then just see a single tool appear on the end of your workflow and then you configure it like any other tool. Hope this helps!

[Beckyli]

if i already have Macro on my workflow, how should i add to it? it show me below error.

[DataNath]

@Beckyli you just need to insert it into your main workflow where you want to output to multiple files. There’s no need to open up the macro itself or do anything to it!

[Beckyli]

thanks, i got it.

[rickey0830]

Neat! This will be super useful. Thanks for creating it :) 

[KiaMoua]

I'm new to Alteryx... still working on baby steps but WOW... thanks so much!!!!

[Hamder83]

Hi @DataNath 

I have a workflow where i output 4 different csv files, with different schemas and content in general.

Can I modify the macro, so it takes several inputs, and outputs that to each sheet?

[DataNath]

Hey @Hamder83 - I don't believe you'll be able to do this. As your outputs have different schemas, you'll need to deal with them in 4 separate streams within the workflow whereas my macro splits a consistent data set based upon the values of a selected field. For your approach, you'll need to keep each output in a separate branch of the workflow and use block until done tools to control the outputs.

[KiaMoua]

Is there a way to password protect the output files? I'm using this macro but then I have to go into each file and password protect the file after. Any help would be WONDERFUL!!!

[DataNath]

Hey @KiaMoua, the only way to achieve this is with a macro as far as I'm aware, or leverage the R (and could probably use Python) tool - there's a few of them floating around the community if you have a search. However, I think you'd then just need to take parts of both my macro and those to roll everything into one. It may be something I look at in future but just don't have the time at the minute!

[KiaMoua]

Thanks, I'm a pretty new user for Alteryx and don't have the skills to chop up the macro and combine but that is my hope that I will be able to one day... thank you

[EeJun]

Hi, not sure if anyone encounter issue like "Unable to create backup of ....xlsx to ...bak: Access is denied." issue using this macro?

[Tmanuela]

amazing tool, thanksss

[alexanderpiercekpmg]

Great work with this macro! I encountered an issue when trying to output to multiple sheets: "You must specify a sheet name." However, I do not know how to specify a sheet name, since there only seems to be an option to input file name. How can I work around this error?  

[DataNath]

Hi @alexanderpiercekpmg, the macro is designed to allow you to split the output into different sheets based upon a field within the data. As you can see here, I can choose one of the two fields within my dataset. Do you not have this option? Can you send me a message with some screenshots/a workflow if not? Thanks!

[aanderson99]

@DataNath this was incredibly helpful and useful - thank you! 

[gunnarscheck]

Thank you for posting this macro, it was very helpful for completing my workflow! Will definitely be using plenty more in the future

[MarjorieNT]

Hi, follow up question please. Is there a limit to the number of sheets that can be outputted within a file? e.g. i need to output 49 sheets/tab - is this possible?

[DataNath]

Hi @MarjorieNT, as far as I'm aware the sheet limit is over 1 million so you should be fine with 49!

[number1woman]

Hi! I appreciate your work. I was able to get it to work for me. Is there an option to overwrite the excel files? Thank you!

[DataNath]

Hi @number1woman - great to hear! If you go into the macro itself (right-click on the tool when it's on the canvas) and edit the configuration of the Output Data tools inside, then re-save it, you'll be able to do so. Depending on your use-case (multiple files or multiple sheets), you'll just need to set the configuration to one of the overwrite options:

[Fadi313]

Hi,

I got the below error once I tried to write to multiple sheets. if you can help please

Multiple Excel Outputs (2) Tool #33: 12 records were written to "C:\New folder (12)\Testt.xlsx" (6010)

Multiple Excel Outputs (2) Tool #33: 8 records were written to "C:\New folder (12)\Testt.xlsx" (7032)

Multiple Excel Outputs (2) Tool #33: 8 records were written to "C:\New folder (12)\Testt.xlsx" (7134)

Error: Multiple Excel Outputs (2): Tool #33: Can't open file: C:\New folder (12)\Testt.xlsx: The process cannot access the file because it is being used by another process. (32)

[Raj_007]

Hi DataNath, are we not supposed to edit the macro to input our file location, file name, sheet names etc

[DataNath]

@Raj_007 hey! You just need to complete the configuration options on the macro and those parameters will be replaced and allow the macro to run properly.

[Raj_007]

yeah, i did replace them with my location, file name, sheet name - its weird that it says sheet name already exists when i run the 2nd time

[Raj_007]

do you mean we just input the file location, file name in the workflow not the macro workflow?

[Fangfang]

hi when i download and open the macro, it shows like below, how can i select all sheets of my input file? Thanks.

[DataNath]

Hey @Fangfang - you don't need to open the macro. Just right click on your main workflow > add > macros and then navigate to where you saved it. From there, just configure it as you do with any other tool!

[Fangfang]

may i ask, if my input file already have multiple sheets, how to split them to multiple excel files? seems this tool only support split 1 sheet that have mutiple rows by selected column.