Export multiple tables to individual Excel sheets

[antoine_pac]

Hi, I'm new in Alteryx and I face an issue.

I'm dealing with multiple tables (say "table1", "table2", "table3") that I want to integrate in individual excel files.

The problem is that I don't know how the "table1" can replace an existing sheet called "data1" in a first excel file, the "table2" can replace an existing sheet called "data2" in a second excel file, etc...

Thanks for your help

[mbarone]

I think your best bet in this case is to use an output data tool.  Check out this thread:https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Output-to-multiple-sheets-within-an....  

[antoine_pac]

I'm afraid that the output tool does not work... But let me explain what I want to do more clearly, sorry.

I have an excel file "employees.xlsx" including 2 sheets: an "IHM" which must not be modified and a "data" which contains several columns including IDs (see screenshot).

The goal is to create an excel with the "IHM" sheet unchanged + a filtered "data" sheet for each ID.

Concretely, I would like an excel file "employee_7003343E.xlsx" with the same "IHM" sheet and the "data" sheet filtered for ID = 7003343E. Same for the IDs xxxxx and 7003343Y.

Thanks a lot for your help !

[atcodedog05]

_

[atcodedog05]

Hi @antoine_pac 

I read through your requirement. Sadly Alteryx overwrites all existing sheets when report tool is used. Hence your IHM sheet will be removed. If all sheets are coming from report tool then its possible to output to multiple sheets.

[mbarone]

I still think the Output Data tool is the way to go - indirectly.  It sounds like you want one XLSX file for EACH user.  The individual XLSX files will have TWO tabs: one tab with the user info (data tab with just the record for one user), and one tab with the IHM tab, as-is.  And the name of all these XLSX files will have the username in it.

That being the case, a batch macro that loops through each user and then creates one XLSX as described above.  

For example:
user1.xlsx has data tab with user1 record and full IHM tab.

user2.xlsx has data tab with  user2 record and full IHM tab.

and so on.

Unless I'm still not understanding the requirement.

[antoine_pac]

Exactly what I want to do !

Ok, I didn't know what we could make with batch macro in that case... (I am brand new and I have not had the opportunity to handle macro).

So this will do the job for any number of user ?

[mbarone · Accepted solution]

Correct.  It will work for any number of users.  Batch macros can be tricky if you've never worked with them before.  Here's some reference material to get you started:
https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Getting-Started-with-Batch-Macros/t...

https://community.alteryx.com/t5/Interactive-Lessons/Creating-a-Batch-Macro/ta-p/657923#done

https://community.alteryx.com/t5/Videos/Build-Your-First-Batch-Macro/td-p/52900

I've attached a very simple workflow package with a basic example.  There's a workflow (YXMD), master spreadsheet (XLSX), and the batch macro to do what you need (YXMC).  Download and open the files and see if you can work your way through it.

[antoine_pac]

Perfect, thanks a lot for your help ! 😀

[mbarone]

Welcome!