loop through files in dir

[detlion1643]

I have some code that takes a column and compares it to a column in a backup file. The code I have works if i specify the workbook to open. I already have code that gets the filename of the currently open workbook. What I am looking for is a way to loop through each .xls file in a directory. Basically open a file, get filename (already have), run code (already have), then close file.

For each *.xls in *
get filename
run code
close file

I've never been good with fso's or the bunch.

 

[texasalynn]

I use this to close file, where I assign the file a name (bFilename)

Workbooks(bFilename).Close

 

[VoG]

Try something like

Sub ProcessFiles()
Dim MyFolder As String
Dim MyFile As String
Dim wb As Workbook
MyFolder = "C:\example"
MyFile = Dir(MyFolder & "\*.xls")
Do While MyFile <> ""
    Set wb = Workbooks.Open(MyFolder & "\" & MyFile)
    '
    'code here
    '
    wb.Close
    MyFile = Dir
Loop
End Sub

 

[detlion1643]

Awesome Vog, working great.... one more thing if you don't mind. Is there a way to only open files which have a certain string in the name? EX) aa.xls, dg Maps.xls, df.xls, fh Maps.xls to only open the ones with Maps in the filename?

 

[VoG]

Try

Sub ProcessFiles()
Dim MyFolder As String
Dim MyFile As String
Dim wb As Workbook
MyFolder = "C:\example"
MyFile = Dir(MyFolder & "\*.xls")
Do While MyFile <> ""
    If MyFile Like "*Maps*" Then
        Set wb = Workbooks.Open(MyFolder & "\" & MyFile)
        '
        'code here
        '
        wb.Close
    End If
    MyFile = Dir
Loop
End Sub

 

[detlion1643]

Thanks VoG!

 

[WadeExcel]

In about ten days I will have a need to do the same thing to about 30 *.xls files regularly. This will be the perfect way to loop through those 30 files regularly and run the same macro on each file. Glad you posted this.