Best way to import data from many several workbooks
 

I recently designed a data collection tool sent out to reps around the
country. Each rep was instructed to save their file with his/her State
abbreviation and send it back to me. So that means i have 50 files (1 for
each abbrev).

I need to pull all the data entered into one workbook that lists all the
States (each row contains the State Abbreviation in column A and to the right
of column A is all the fields from the data collection tool)... What would be
the best way of going about doing this? I am assuming there has to be some
kind of database query or something along those lines, just that I am not to
familiar with any feature like this. I tried using indirect.ext, but it only
works for one cell at a time. When I drag it down to other cells, it crashes
excel everytime.

Can someone point me in the right direction?

Best way to import data from many several workbooks
 

Ron de Bruin has lots of sample code:
http://www.rondebruin.nl/copy3.htm

IntricateFool wrote:

I recently designed a data collection tool sent out to reps around the
country. Each rep was instructed to save their file with his/her State
abbreviation and send it back to me. So that means i have 50 files (1 for
each abbrev).

I need to pull all the data entered into one workbook that lists all the
States (each row contains the State Abbreviation in column A and to the right
of column A is all the fields from the data collection tool)... What would be
the best way of going about doing this? I am assuming there has to be some
kind of database query or something along those lines, just that I am not to
familiar with any feature like this. I tried using indirect.ext, but it only
works for one cell at a time. When I drag it down to other cells, it crashes
excel everytime.

Can someone point me in the right direction?

--

Dave Peterson