[Amanduh320]

Hello,
I need help concatenating Excel worksheets from a directory into a single sheet. I have some folders with over 1000 files that I need to combine to run analysis on. They all have the same header and data starts on line A37. Is it possible to just put the next file's data underneath the last with some kind of loop?
One problem is that I also need to add a line to distinguish one file's data from another, maybe a column with the file name on each line.
Does this sound possible to automate?
Thank you for your help in advance!

I know there was a previous thread (http://forums.techguy.org/business-a...cel-files.html), but I don't know how to alter it to remove a larger header and I wasn't able to get the macro to work for me.

[DJh6064]

This is something you could use Access for. Import all the excel files and append them into one table with an xtra field to identify the data. You could then export all the data to Excel and create a pivot table. A pivot table is a good way to manipulate dat quickly. Not sure how to automate it the import.

[Amanduh320]

Hi DJh6064, Thanks for replying. The automated import is what I need help on because I have such large quantities of data. I'll give Access a try though.

[Rollin_Again]

Please post a sample file with any sensitive data removed and I'll write the macro for you. What version of Excel are you using?

Regards,
Rollin

[Amanduh320]

Hello Rollin,
I am using Excel 2003.
Here are a few files that I would like to put together. I would be fine taking the headers off completely.
Thanks!
Amanda

[Rollin_Again]

OK now that I have some visual reference please explain in detail what you would like this macro to do. Does it mater what order the data is imported into the master workbook?

Regards,
Rollin

[Amanduh320]

Hi again Rollin,
I would like to combine all the Excel files in a directory to a single file. I need all the header gone, except for Row 1 to be Row 36. I also need a column added that will list the file name that each row is from.
I have thousands of files in some folders and am worried about maxing out Excel's row limit, is it possible to tell it to make a new sheet or file if exceeds the limits?
Order doesn't really matter, but the files go in numerical order throughout the directory and it would be nice if they stayed in that order.
Thanks!!
Amanda

[Rollin_Again]

Will the file structure always be the same? (e.g - rows 1 - 36)

Regards,
Rollin

[Amanduh320]

Yes, they are always the same structure with the header and same columns.

Amanda

[Rollin_Again]

Save the attached workbook to your desktop or other location and then run the macro called "CombineFiles"

The first part of the macro prompt you for the directory containing your data files. This directory should not contain any files other than those XLS files that you want processed. I really didn't get a chance to test the macro so let me know how it works and what changes need to be made to it.

Regards,
Rollin

[Amanduh320]

Thank you Rollin! I will play with it in the next couple days. I really appreciate your time and assistance.
Amanda