Advice with Looping Through Files in a Folder

largeselection

Active Member
Joined
Aug 4, 2008
Messages
358
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!
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Take look at the Dir command in help. You will want to change the MyPath assignment:

Code:
' 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
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top