Combine sheet from multiple excel files into one file

Praneeth1996

Hi team,

I have around 400 files. I want to extract all the sheets with name "Apple" from these files and put those into a new excel file as different tabs. However, not all files can have this particular sheet.

If there are 40 files where this sheet is present, I want to make an output file with 40 tabs. Is this possible?

Workflow

Datasets

Input

Output

Tips and Tricks

Macros

Best Practices

Apps

Batch Macro

Accepted answers

All comments

mceleavey

Hi @Praneeth1996 ,

I've attached an example workflow with a couple of tools to do exactly this thing.

The first custom tool identifies the sheet names in every workbook. You can apply a filter to simply take the "Apple" sheets. The second one loads them all in together.

I hope this helps,

Multi Sheet Import Example 2.yxzp

Praneeth1996

Hi @mceleavey ,

In the .yxmc files - Dynamic Input files says file doesn't exist. Do you want me to select one of the 400 files here?

Error.png

mceleavey

@Praneeth1996 , you can close the macro, you don't need to open those.

You should see the workflow:

In the first tool, the Directory tool, point it to the folder containing the files you want, and input the generic part of the filenames (if you have any, otherwise simply *.xlsx).

Then filter after the Dynamic Multi File Sheet Names tool to only those sheets named "Apple".

Praneeth1996

Thank you @mceleavey.

However, I am not getting an error for 2nd Macro tool. What needs to be done here?

Error1.png

mceleavey

@Praneeth1996 , you have not mapped the control parameter.

Click on the tool, go to the "Questions" tab and select "fullpath".

Praneeth1996

Hi @mceleavey - The Output is consolidated into one piece of information. How do I bifurcate into different tabs? Can you please help.

mceleavey

@Praneeth1996 , I thought that's what you wanted? This will have loaded in every "Apple" tab from all files, due ot the filter after the first customer tool, right?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703