This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.