Extracting data from multiple excel files

Hi all,

I am a beginner in macros and am hoping to get some help from here!

Basically I have a number of excel files with very similar content. They are all tables with headings like "ID", "NAME", "RATING1", "SUBJECT", "RATING2", etc. but they arent in consistent order in all excel files.

They are all in the same folder. They will be updated from time to time and new files will be added too.

What I am trying to do is to create a master file which I can extract only a few necessary columns and put them onto the same sheet. Eg only "ID", "RATING1" and "NAME".

Any help or hint on how this can be done would be much appreciated!! Thank you VERY much!!

TF

Hi,

It would be helpful if you posted a dummy worksheet showing what your original data looks like and how you envision the final sheet to look like. JBeaucaire helped me with a similar problem and that may give you an idea where to start http://www.excelforum.com/excel-prog...-workbook.html.

Good luck.

abousetta

Hi abousetta,

Thank you for your information. Attached is a dummy version of my original data and the master file that I imagined would look like. There are about tens of these "Dummy1" files in the same folder, and I am hoping to extract a few necessary columns from the data files to one Master file like the attached. Not sure if its possible?

I am still trying to understand the code and changing bits and pieces to see if it works on mine! But thank you again!

TF

Hi,

Try this:

Please Login or Register  to view this content.

Its a modified version of the one I am using. The original code was written by JBeaucaire. Hats off to him. You may want to add some lines for formatting if you prefer, but I wasn't sure if the color scheme, column and row width were what you wanted and so I did not add them into the code.

Good luck.

abousetta

Thank you very much abousetta (and JBeaucaire too). Really appreciate it!

It works very well apart from a little problem, would it be possible for the code to pick out columns by their heading names rather than the column letters?

Still googling it, any ideas or hint would be much appreciated!