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?

[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,

M.

[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?

[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".

M.

[Praneeth1996]

Thank you @mceleavey.

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

[mceleavey]

@Praneeth1996 , you have not mapped the control parameter.

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

M.

[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?