Combine multiple sheets into one sheet

[ppatane]

I have multiple files, each with multiple sheets. All sheets have the same column headings. I need to combine them all into one with a column for the sheet names and a column for the file name. I cannot figure out the tool(s) to use. The Join and Join Multiple aren't working for me, at least how I am using them.

[KimLamNg]

You will need 2 batch macros, one to read the sheet name (Input Data tool to import sheet names) and one to read the file name (Directory tool)

[ppatane]

Thank you, KimLamNg. That helps a lot. But I still do not k now how to combine all the data from all the sheets into one.

They all have 3 headings: Date, Title, Amount and then hundreds of rows of data underneath.

I need to have part of the filenames and the sheet names as columns in front of those.  I see how to use the examples you gave me to get that specific data, but it's the combining that I am not getting

[Yoshiro_Fujimori · Accepted solution]

@ppatane ,

One way of doing @KimLamNg 's direction.

Main Workflow

Macro to Get Path and Sheet names

Macro to combine sheets

You may want to tweak macros to add more file information.

Good luck.

[Yoshiro_Fujimori]

You may also find this article interesting.

Alteryx & Excel Guide

[ppatane]

I accepted the solution provided by Yoshiro_Fujimori, even though I am a beginner and need to investigate it intensely.  Lots of learning to do!

[kmcgraw2]

Hi! How does this change if I am using the directory tool to pull in multiple files. Some as xlsx and others are xlsb