Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here

Combining tabs from multiple files into a single excel but keeping all the tabs separate

Highlighted
7 - Meteor

Hi

 

Struggling with a problem at the moment and would appreciate any help on this.

 

I have c.300 files all with 2 tabs in each file. For context each file is for a specific customer, the front tab is their address and the second tab is the details of the lettter.

 

What i want to do is combine the 2 tabs in each of the 300 files into a single excel but maintain the separation of the information into the tabs. The reason for this is i have a VBA macro that i found which will print all 600 tabs into PDF which i can then consolidate and turn into a single pdf and send to the printer.

 

The outcome would be an xls file with 600 tabs. i do not want to combine all the sheets into a single excel sheet. The reason for this is each address tab and each letter tab needs to be maintained on a different piece of paper and therefore needs to be a separate pdf.

 

Any help on this would be massively appreciated.and would save me the joy of clicking print on 300 excel files.

 

Thanks!

Highlighted
Alteryx Partner

Hello Jag,

 

Could you post a sample of your excel file?

Highlighted
7 - Meteor

I've attached some dummy data

 

I have around 300 files all exactly the same lay out but with the order data for each customer split out into a file each per customer. What would be great is if these two files got combined into a single Workbook but with 4 sheets.

 

The reason i need to keep the sheets separate is because each tab needs to be printed as a pdf which will then be sent to each customer. Therefore i need 1 tab per customer order.

 

Also due to the template used, the corresponding tabs in each file are titled the same which i imagine may cause issues when merging them into a single file. Is there a way round this? perhaps making the tab names dynamic and using the title of the file?

 

thanks for any help im really stuck on this one!

Highlighted
Alteryx Partner

Hello again,

 

You could try this:

 

1. Use the Directory Tool to get the full path unitl a file

2. Assuming that sheet names are the same for all files, use a Text Tool with 2 lines, one for each sheet name, + |||, by the end, you should have |||Sheet1 in line 1 and |||Sheet2 in line 2, for example

3. Use the Append tool to join the sheet names from the Text Tool in step 2 to the Directory Fool output and then concatenate the field Full Path with the sheet names, you should have something like this "C:\location\filename.xlsx|||Sheet1"

4. Use filter to divide data stream into data from sheet1, data from sheet2. Use Dynamic Input in each of them.

5. Use Record Tool + Formula Tool for dynamic sheet names with the full path to the workbook that will receave all the data

6. Flag this option Take File/Table Name From Field/Change Entire File Path, using the Full Path with sheet names that you built on step 5.

7. Repeat this for the other data stream

 

Let me know if it works

Labels