Is there a better way to combine Excel Workbooks in Alteryx?

[alteryxisconfusing]

I was able to figure out a way to combine my Excel workbooks so it results in a Workbook with multiple sheets, but it's very tedious since I have to create a flow for each individual sheet. Is there a better way to do this? I read you can use a macro, but I get confused when I try taking that approach and it keeps merging all the data into one sheet (not the desired end result).

The way I do this now is using the "data input" for the workbook/sheet, "block until done", and "data output" for the individual sheet. This results in the desired combined Excel workbook with multiple sheets.

So what I have looks like this:

Data Input = Workbook1.xlsx

Workbook1.xlsx

Sheet1WB1

Block until done > Data Output (Results.xlsx)

Then

Data Input = Workbook2.xlsx

Workbook2.xlsx

Sheet1WB2

Block until done > Data Output (Results.xlsx)

Then

Data Input = Workbook2.xlsx

Workbook2.xlsx

Sheet2WB2

Block until done > Data Output (Results.xlsx)

This results in the following Output

Results.xlsx

Sheet1WB1

Sheet1WB2

Sheet2WB2

a single Excel workbook with multiple sheets (desired result).

[CarliE]

@alteryxisconfusing,

Hi! 

There are a few ways to do this automatically, just one question here:

Is the schema on the different workbook the same? (Same headers)?

[alteryxisconfusing]

Different schemas, so I think this calls for a macro right? Every time I try using a macro it just merges all the data into one sheet instead of resulting in a workbook with multiple sheets/tabs.

[CarliE]

@alteryxisconfusing ,

Yes a macro is required. I will put something together right now and reply in a few minutes

[alteryxisconfusing]

Thank you! 

I don't know why I keep struggling with the macro part. I'm extremely new to Alteryx, so really simple (explain like I'm five) kind of notes and layout are super helpful! I feel like the macro should be fairly simple for this right? 

Basically the process would be look at a folder with the excel files, read the workbook filenames, and then identify the sheets in each workbook, then take each individual sheet (using the original sheet name unless there's a duplicate), then resulting in a single workbook with multiple sheets.

[CarliE]

@alteryxisconfusing  you are exactly right!

There are 2 macros in the workflow, the first is to grab the sheet names from the workbooks in the directory and the 2nd one is to pull the data and push it to an excel file dynamically by the sheetname values.

Attached is the workbook so go ahead and run it! , I have annotated the different pieces in the workflow for you so if you have any questions, please ask! 

If this helped to solve your problem, please make sure to mark it as a solution

[alteryxisconfusing]

Thank you so much for making this! Mind if I ask a few questions to understand it more? I'm kind of struggling to change the file paths.

Questions:

1. It looks like the "directory" tool I just need to point to the folder with the files, correct?

2. In the second tool, "Getsheetnames.yxmc" I right clicked and did "Open Macro". How come it needs that file "Quarterly Subscription Data.xlsx" ? Is this a frame of reference for the macro so it knows what operation to perform?

3. After closing the first macro I looked at the "filter" and I changed the workbook and sheet names to the real ones I need. 

4. I didn't touch the "formula", since it looks dynamic.

5. The last macro "Pullfiles.yymc" is what throws me off though - is this using "workbook1.xlsx" as a reference as well? How do I change where the output file goes? 

[alteryxisconfusing]

Okay I looked through it to try and understand it for a few hours and it seems to be working. I even changed the workbooks in the macro to see how those factor in.

Couple other questions:

1. What happens if two sheets have the same name? Is it possible to add logic to the formula so it will use a different name or is there a way to select a different name for the sheet in the formula (e.g. the sheet is called "sheet1", but I want it to show as "SheetA" in the output).

2. How can I arrange the sheets when generating the output? I thought the formula would be the same way they get added, but that's not the case.

[CarliE]

hi @alteryxisconfusing,

Questions:

1. It looks like the "directory" tool I just need to point to the folder with the files, correct?

 - this is correct

2. In the second tool, "Getsheetnames.yxmc" I right clicked and did "Open Macro". How come it needs that file "Quarterly Subscription Data.xlsx" ? Is this a frame of reference for the macro so it knows what operation to perform?

- this is just a temporary file, the files from the directory are going to update the path that is being pulled based on whats being fed in

5. The last macro "Pullfiles.yymc" is what throws me off though - is this using "workbook1.xlsx" as a reference as well? How do I change where the output file goes?

- this is the same as question 2, also a reference file that will be replaced with the file and sheet specified 

6. What happens if two sheets have the same name? Is it possible to add logic to the formula so it will use a different name or is there a way to select a different name for the sheet in the formula (e.g. the sheet is called "sheet1", but I want it to show as "SheetA" in the output).

- use a formula to change it ( if [sheetname]='sheet1' then 'sheetA' else [sheetname] endif )

7. How can I arrange the sheets when generating the output? I thought the formula would be the same way they get added, but that's not the case.

- I have to test this out but i believe its in the order in which it goes into the pullfiles macro

Highlighted in yellow tells you what needs to be updated, everything else can stayed configured the same way.

please make sure to mark as a solution if it solves the problem!

[#10]

Questions:

1. It looks like the "directory" tool I just need to point to the folder with the files, correct?

 - this is correct

2. In the second tool, "Getsheetnames.yxmc" I right clicked and did "Open Macro". How come it needs that file "Quarterly Subscription Data.xlsx" ? Is this a frame of reference for the macro so it knows what operation to perform?

- this is just a temporary file, the files from the directory are going to update the path that is being pulled based on whats being fed in

5. The last macro "Pullfiles.yymc" is what throws me off though - is this using "workbook1.xlsx" as a reference as well? How do I change where the output file goes?

- this is the same as question 2, also a reference file that will be replaced with the file and sheet specified 

6. What happens if two sheets have the same name? Is it possible to add logic to the formula so it will use a different name or is there a way to select a different name for the sheet in the formula (e.g. the sheet is called "sheet1", but I want it to show as "SheetA" in the output).

- use a formula to change it ( if [sheetname]='sheet1' then 'sheetA' else [sheetname] endif )

7. How can I arrange the sheets when generating the output? I thought the formula would be the same way they get added, but that's not the case.

- I have to test this out but i believe its in the order in which it goes into the pullfiles macro

Highlighted in yellow tells you what needs to be updated, everything else can stayed configured the same way.

[alteryxisconfusing]

Ahh thank you for those helpful clarifications! I think I'm starting to see it more clearly now. I'll test out the formula you mentioned for the sheet names so I can easily recast them into the output.

One last thing I'm trying to add is formatting to make sheets. What would it look like to do this? Ultimately, I'm trying to make it look like a pivot table where people could filter the data and maybe I would have some cella colored or borders formatted. 

I read the table and render tools can do this, but I'm confused how to add it for specific sheets at this point.