Reading in the files in a directory into an array, directory chosen by user

HI,

I have some qty of excel files. All the files are the same except for the name. I need to open each file and copy the data. I will paste the data from each file into a single file.

What i was wondering is does anyone have a good program to do the following:

1) User selects a diretory
2) After directory selected you populate the array with all the excel files in the directory

any help would be appreaciated. thanks.

Hi Leith Ross,

Nice! Works great!!!!!!!!!!!!!!!!!

Thanks alot!

Few quick questions:

1) To call this function do i have to create an array of type variant or can it be an array of type string?

2) The entries in the array have the full "Path" as well as the actual file. How do i strip out just the file itself from the string..........if you know a quick fix please let me know. The stripping out the file name only can be done in a separate array if you want.



Thanks again........your soln works great!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Just noticed that when i put this into my actual program.........i get an error. Apparently in my program i have at the top of the macro

Option Base 1


Apparently in this program your starting at "0" .....how to change your code so that it starts from "1"?

ok...i just added cnt=1 prior to the Do loop

Please Login or Register  to view this content.

and now it starts at 1.

thanks.