We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes.
Learn more now.
I recently had a session in our Virtual Solution Center when a user asked me about the possibility to simultaneously output multiple excel files with multiple sheets, however, with a peculiarity: he needed to output different files based on column X, and each file needs different sheets based on column Y.
Code: Column used to create the output files: Output_1.xlsx ; Output_2.xlsx
Separator: Column used to create the sheets: Output_1.xlsx with 3 sheets (A, B, C) ; Output_2.xlsx with 2 sheets (A, B)
The Output Data tool is used to write the results of a workflow and can be used to write distinct results for each value in a selected field. So, it's possible to create different files based on field or multiple sheets in the same file based on field.
2 - Create a formula to update the file name based on the field of the Control Parameter:
Step 4 - Save it!
Now we have our batch macro configured to receive the input data and to use the [Code] field to create multiple files with different sheets at the same time.
Using the Macro
First, drag and drop a Summarize tool in order to eliminate duplicate [Code] values. Next, set up the [Code] field in the first anchor of the macro: Control Parameter. Finally, set up the [Code] field in the second anchor of the macro: Group By and Input Group By
1 - Drag and drop a Summarize tool:
2 - Set up the [Code] field in the first anchor of the macro: Control Parameter:
3 - Set up the [Code] field in the second anchor of the macro: Group By and Input Group By:
Et voilá! Run your workflow and take a look at the results 😃
Note: Attached is a sample workflow for demonstration purposes, and not for use in a production environment.