Output to Multiple Excel Files and Tabs

[nadiahghzli]

Hi, I am currently planning to generate multiple excel files and tabs based on two columns. The idea is illustrated as below:

Does anyone know how to generate output as above? Preferably with the naming as well (i.e., not the default name of the output sheet e.g. Sheet 1, Sheet 2 etc.). Thank you in advanced !

[FrederikE]

Hey @nadiahghzli,

You need to adjust the file path, but this should do:

[binuacs]

@nadiahghzli One of doing this

[nadiahghzli]

Thank you so much for replying to this post ! However, I am unable to proceed with the suggested methods as I am dealing with a very complex data (e.g. numerous input). I have attached an example of the workflow and sample data. Currently, the workflow would generate 2 excel files (i.e., Bookmongers & Foyles) but only one tab that consists of all the genre. 

I need to generate excel files based on the bookstore names (i.e., Bookmongers & Foyles) and a maximum of 4 tabs (i.e either or combination of 4 genres).

Example:

Appreciate if anyone could assist me on this. Thank you !

[FrederikE]

Hey @nadiahghzli,

I am not able to rebuild this working as i dont understand what is your macro trying to achieve. I am not sure I quite get it. The first filter always filters out everything as it seems. 

Also to send workflows much easier: 

This will save a ".yxzp" a packed workflow including all data. 

[nadiahghzli]

Hi, FrederikE. My apologies. The macro included is a batch input. I have attached the .yxzp and the batch input macro for your perusal. Thank you very much for your assistance !

[FrederikE]

Hey @nadiahghzli,

Yes, I can open the workflow. I don't understand why you are using this macro - This macro is what is not functioning correctly. Without understanding it I can't fix it. Your first filter always filters out my input Excel file path (attached screenshot). What is this filter doing, and why and what is with the next one(s)?

If you just want to separate Excel files, you can use something like 

CONTAINS([Path],".xlsx")

But overall, what should the macro output? Can you maybe give me an example of what you want there? 

[nadiahghzli]

Hi, FrederikE !

The macro's purpose is similar to a dynamic input tool, however, the input files doesn't need to be on the same schema. I have used the macro to combine the same tab between two excel files (as the two tabs has different set of columns), which would then generate the following outputs:

I have attached excel files (i.e., manually prepared) that consist of what I am planning to generate from the overall workflow. Thank you !

[FrederikE]

Hey @nadiahghzli,

So this got quite messy, I am not really able to figure out what you do and why you do it, so I restarted it. I have provided a workflow that reads all Excels including all sheets and unionizes all the data. 

I would recommend a separate process for non-excel files to not mix this logic up (F Output of the 1st filter) and then union it later on. 

Main Flow:

Macro to read sheetnames:

Macro to read all Sheets: 

[nadiahghzli]

Hi, FrederikE,

Thank you for the suggestion on the input ! However, I would like to separate the table between the tabs "Bought" and "To Buy" as I would like to maintain the columns name and not unionizes the data. (please note that the info provided is a sample data and I am currently working on a very complex data).

Furthermore, appreciate if you could assist me on the output part of my workflow as I am currently stuck on that. I would need to generate excel files based on the bookstore names (i.e., Bookmongers & Foyles) and each files contains a maximum of 4 tabs based on the genre.

My apologies if I am not clear. This is the third day that I am using Alteryx. Really appreciate your constant help !