List excel files in folder but exclude ones with "old" at the beginning

[FFischer]

Please help with solving a macro. I need a routine that will list all Excel files in a specific folder, but exclude the ones that begin with the word "Old". If push comes to shove, I could simply list all files, then use Excel's filtering functionality to accomplish, but I am trying to avoid that workaround and use only as a last resort. Thanks for any guidance that can be offered.

 

[JoeMo]

Please help with solving a macro. I need a routine that will list all Excel files in a specific folder, but exclude the ones that begin with the word "Old". If push comes to shove, I could simply list all files, then use Excel's filtering functionality to accomplish, but I am trying to avoid that workaround and use only as a last resort. Thanks for any guidance that can be offered.

After you enter the path to the specific folder where noted below, see if this does what you want.

Sub ListFilesIfNotOld()
Dim pStr As String, myFile As String, newSht As Worksheet, nR As Long
'Enter your folder path in next line
pStr = "Folder path goes between these quote marks" & Application.PathSeparator
myFile = Dir(pStr & "*.xls*")
If myFile = vbNullString Then Exit Sub
Application.ScreenUpdating = False
Set newSht = Sheets.Add(before:=Sheets(1))
With ActiveSheet
    .Name = "FileList"
    .Range("A1").Value = "Files"
    If Not LCase(Left(myFile, 3)) = "old" Then
        .Range("A2").Value = myFile
        nR = .Range("a" & Rows.Count).End(xlUp).Row + 1
    End If
End With
Do Until myFile = vbNullString
    myFile = Dir()
    If Not LCase(Left(myFile, 3)) = "old" Then
        newSht.Range("A" & nR).Value = myFile
        nR = newSht.Range("a" & Rows.Count).End(xlUp).Row + 1
    End If
Loop
newSht.Columns("A").AutoFit
Application.ScreenUpdating = True
End Sub

 

[FFischer]

Outstanding! Thanks for the quick response. The LCase is what I was missing. Thanks again!

 

[JoeMo]

Outstanding! Thanks for the quick response. The LCase is what I was missing. Thanks again!

You are welcome - thanks for the reply.