Advice with Looping Through Files in a Folder

[largeselection]

Hi,

I have some files that are fairly tedious to process. Basically all I have to do is open a report (it's in .txt delimitted format), filter a column for a value, copy the results of the filter and paste them into a sheet in a different workbook. Then I repeat for each report in the folder.

What I'd like to do is automate it a bit. I have the code done for the filter and copy/paste. What I need some advice with is how I can get it to loop through all of the files in the folder I specify.

At this point, I have code where the user clicks a button and a file search dialog pops up, they navigate to the folder where they have saved reports and that path is saved on the first page of the workbook. So I have the path established.

I also have the code written which will filter/copy the information from the report and paste it in the appropriate tab.

I figured the easiest way to do this would be to use some sort of loop. Something like:

-User clicks button
-User navigates to the folder containing the .txt delimitted reports to process
*-Code counts how many files exist in the folder specified
*-Opens the first file and names it "MyFile"
-Do Code to filter/copy information from MyFile to ThisWorkbook
*-Closes MyFile
*-Opens the next file and names it "MyFile" again
*-Repeat, repeat repeat until all files have been processed

1) Does this seem like a reasonable approach?
2) How can I do something like this?
The points with "*" in front of them are the ones I'm having difficulty with. Though the Closes MyFile part I can probably figure out and the "Opens the next file and names it "MyFile" again" would be accomplished with a loop as it does the next file in the folder.

So I think the only part that's holding me up is if I have the path where the folder is, how do I translate that into "count the number of files in there, then for each file in there..., then next file until there are none left"

Thanks!

 

[tlowry]

Take look at the Dir command in help. You will want to change the MyPath assignment:

' Display the names in C:\ that represent directories.
MyPath = "c:\"    ' Set the path.
MyName = Dir(MyPath, vbDirectory)    ' Retrieve the first entry.
Do While MyName <> ""    ' Start the loop.
    ' Ignore the current directory and the encompassing directory.
    If MyName <> "." And MyName <> ".." Then
        ' Use bitwise comparison to make sure MyName is a directory.
        If (GetAttr(MyPath & MyName) And vbDirectory) = vbDirectory Then
            Debug.Print MyName    ' Display entry only if it
        End If    ' it represents a directory.
    End If
    MyName = Dir    ' Get next entry.
Loop