loop through all text files in a folder and subfolders 1

[jay9333]

Hello,

I've got thousands of tab-delimited text files in one folder and its 20 or so subfolders (which themselves have subfolders) that look like the following:
####################
PROTOCOL LABID ASSAYID SPECID PTID VISITNO...
045 XG CF30-07 W004406 124050034 06...
045 XG CF30-07 R021603 123050216 06...
045 XG CF30-07 S021806 126050018 06...
####################

I need to know how to loop through all of them and append data from them into one worksheet. I have the problem mostly solved, except for the looping through the filesystem part. I can open a text file with no problem, loop through it, and append its data to an excel worksheet. But my XL VBA book doesn't mention how to loop through files. I'd like to open and append data from every ".txt" file in a specific folder and all its sub folders.

I've written a Macro that allows a user to select one tab-delimited text file. It then goes through that file and copies the columns I need to a "masterList" worksheet (the columns happen to be the 1st, 3rd, 5th, and 6th columns). The macro is run from a blank "masterList" worksheet which is then filled with the data. Is there some way to wrap this in one big loop that goes through the folder and all its subfolders until every file ending with ".txt" has been openned and had its data appended? There are other files in the folders, but all the files ending in ".txt" will be sent data files that should be appended. Doing this by hand is not an option, of course, so the most important part of this macro is the part that hasn't been written... looping through all of the files.

If have any code I could look at or know of any rescources that would direct as to how to do this, I would appreciate it very much.

Thanks,

 

[PHV]

Something like this ?
With Application.FileSearch
.LookIn = "\path\to\dir""
.FileName = "*.txt"
.SearchSubFolders = True
If .Execute() > 0 Then
For i = 1 to .FoundFiles.Count
YourAppendMacro .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jay9333]

Heck Yeah, PHV, that is exactly what I needed. Sometimes I just don't know where to look for this stuff. Thanks!!