Can I combine two Excel Workbooks with multiple sheets on each into one workbook?

[alteryxisconfusing]

Is it possible to combine 2 or more Excel workbooks into one? 

I saw multiple solutions posted on this, but they keep merging into one sheet, I would like to preserve the multiple sheets in each workbook - the column headers are different in each sheet.

For instance I have a folder with Workbook1.xlsx and Workbook2.xlsx and I would like the output to be Combinedworkbooks.xlsx (which would have all the preserved sheets in it).

Workbook1.xlsx looks like this:

Workbook1.xlsx

Sheet1WB1

Sheet2WB1

Sheet3WB1

Workbook2.xlsx looks like this:

Workbook2.xlsx

Sheet1WB2

Sheet2WB2

Sheet3WB2

My desired workbook/output would be Combinedworkbooks.xlsx

Which I want to look like this:

Combinedworkbooks.xlsx

Sheet1WB1

Sheet2WB1

Sheet3WB1

Sheet1WB2

Sheet2WB2

Sheet3WB2

[grazitti_sapna]

Hi @alteryxisconfusing , you can use the below workflow to achieve this. The first macro will read the file names and second one will read the data from all files. You can also use the same workflow in case your schema is different for the files.

[alteryxisconfusing]

Hi,

Thanks for the response! I'm getting an error though on the "reading_files".

Here's what I did:

1. I switched the directory to the test one I'm using with my Excel files.

2. I ran the flow.

The error is on the "reading_files" (5th tool). It shows things like "Record #1 Tool #4 does not match a sheet or named range in workbook2.xlsx or the file is corrupted".

(workbook2.xlsx is the name of one of my excel workbooks).

[grazitti_sapna]

@alteryxisconfusing ,Can you share your files? I will have to check the flow through it because for me its working fine\

[alteryxisconfusing]

Attached are my workbook files.

Just want to combine these two workbooks (or more than two) into one merged workbook and preserve the sheets in each.

[alteryxisconfusing]

Also, it looks like your image displays the data in one sheet, I want the multiple sheets in one workbook.

So workbook1 has 3 sheets, workbook 2 has 3 sheets, I want the outputted workbook to have 6 sheets (3 from workbook1 and 3 from workbook2). 

My plan is to use this workflow with about 5 workbooks (each with multiple sheets) and that way I can easily combine them into one single workbook with multiple sheets.

[grazitti_sapna]

let me try out with your files. And there are multiple files in my screenshot. Line itme 16 17 data is from sheet2.

[grazitti_sapna]

@alteryxisconfusing , For me its working fine with your files as well. Can you please use this new workflow. And try running it. Please donot change anything as it has your files as well. In case this worked then we can have a discussion on why you are facing issue while changing directory path

[alteryxisconfusing]

Hi,

Thanks again for the response on this! Is it not possible to output a workbook with just the sheets in it? 

It looks like the output is just taking the data from each sheet and outputting a single sheet with the data.