Opening multiple files

[etpeterson]

I currently have a spreadsheet that takes a folder location from the use that is entered into a cell and opens 10 files in that folder, 1.txt-10.txt and makes seperate worksheets out of each of them. I recorded this macro and excel looks for an exact file name match. I would like to change this so excel will open any .txt file in the specified folder and create a worksheet for each file. Please Help!

Thanks,
Eric

 

[mdavide]

use filesearch:

Set FS = Application.FileSearch

With FS
    .NewSearch
    .LookIn = "your path"
    .Filename = "*.txt"
    n = .Execute
End With

n is the number of files found and FS.FoundFiles is an object where there are the files names found

DM