Combine Excel Files into One

[sammymalta]

Good morning,

I extract a daily report which is always in the same format. Col A to K, it has a header and the data underneath. In a month i have about 20 reports.
I want to combine all the data into one report, (new file) but also add a Col 'L' with the file name from where the data has come.
All excel files will be in the same folder.

I'm sure there have been other posts similar to this, perhaps someone can give me the link as i couldnt find by searching with certain keywords 'multiple', combine, etc

Thanks

Sam

[Olly]

This is straightforward, using Power Query (Get & Transform Data).

Something like:

Please Login or Register  to view this content.

[sammymalta]

Getting a syntax error

Please Login or Register  to view this content.

[Olly]

It's not VBA. As I said, it's Power Query (Get & Transform data).

If you're not sure what to do with the code I gave you, see the link in my signature.

[sammymalta]

Ok got you. Thanks for this!
The only issue i am having is that when i run the query, the file crashes and closes. When i open it again 'recovered' version, the data is there, but it shouldn't stay crashing.
I tried it with 3 files with about 700 rows of data each.

Also, can i remove the sheet name 'Summary', Each file name has a different sheet name; ( a combination of time and date, so it will always be different) (only one sheet per file)

And, the files i have are CSV, and i changed the coding of the extension to .csv but it failed.

[Olly]

Right. So, they're not Excel files, they're CSV text files. That makes quite a difference...

Try:

Please Login or Register  to view this content.

[sammymalta]

Worked perfect the first time. :-)

This function doesnt seem to be available for older versions of Excel right?
As i sent it to someone with Excel 2010 and 2013. And i could not open to 'Edit' the query to change file location.

Sam

[Olly]

Power Query is a (free) addin for Excel 2010 & 2013 - see https://www.excelcampus.com/install-power-query/

It is available as standard from 2016 onwards, labelled as "Get & Transform Data".

Along with PowerPivot, it's the best thing to happen to Excel since the pivot table...