<code>Sub f()
Dim foldername As String
Dim filename As String
Dim lastrow As Integer
Dim allrange As Range
foldername = "C:UsersJay.XieDesktopSW FolderDataData"
a = "*Group*"
filename = Dir(foldername & a)
</code>
<code>Sub f()
Dim foldername As String
Dim filename As String
Dim lastrow As Integer
Dim allrange As Range
foldername = "C:UsersJay.XieDesktopSW FolderDataData"
a = "*Group*"
filename = Dir(foldername & a)
</code>
Sub f()
Dim foldername As String
Dim filename As String
Dim lastrow As Integer
Dim allrange As Range
foldername = "C:UsersJay.XieDesktopSW FolderDataData"
a = "*Group*"
filename = Dir(foldername & a)
I want to search all the files with the word “Group” or “Proportion” in it, is there any ways to achieve that?
I tried using operator in the string itself but it doesn’t seem to work.
I can also create the same code again to so that it runs on the word “Proportion” but I don’t think its effective.
New contributor
Jay Xie is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
4
You can using the Command-Line DIR
<code>Option Explicit
Sub ListFiles()
Const PATTERN = "*Group*,*Proportion*"
Dim FolderName As String
FolderName = "C:UsersJay.XieDesktopSW FolderDataData"
Dim wsh As Object, s As String, f, r As Long
Set wsh = CreateObject("WScript.Shell")
s = wsh.Exec("cmd /c dir /b " & FolderName & PATTERN).StdOut.ReadAll
For Each f In Split(s, vbCr & vbLf)
If Len(f) > 0 Then
r = r + 1
Sheet1.Cells(r, 1) = f
End If
Next
MsgBox r & " files found", vbInformation
End Sub
</code>
<code>Option Explicit
Sub ListFiles()
Const PATTERN = "*Group*,*Proportion*"
Dim FolderName As String
FolderName = "C:UsersJay.XieDesktopSW FolderDataData"
Dim wsh As Object, s As String, f, r As Long
Set wsh = CreateObject("WScript.Shell")
s = wsh.Exec("cmd /c dir /b " & FolderName & PATTERN).StdOut.ReadAll
For Each f In Split(s, vbCr & vbLf)
If Len(f) > 0 Then
r = r + 1
Sheet1.Cells(r, 1) = f
End If
Next
MsgBox r & " files found", vbInformation
End Sub
</code>
Option Explicit
Sub ListFiles()
Const PATTERN = "*Group*,*Proportion*"
Dim FolderName As String
FolderName = "C:UsersJay.XieDesktopSW FolderDataData"
Dim wsh As Object, s As String, f, r As Long
Set wsh = CreateObject("WScript.Shell")
s = wsh.Exec("cmd /c dir /b " & FolderName & PATTERN).StdOut.ReadAll
For Each f In Split(s, vbCr & vbLf)
If Len(f) > 0 Then
r = r + 1
Sheet1.Cells(r, 1) = f
End If
Next
MsgBox r & " files found", vbInformation
End Sub
Check Whether File Names Contain Any String Using Dir
<code>Option Explicit
Sub LoopThroughFiles()
Const FOLDER_PATH As String = _
"C:UsersJay.XieDesktopSW FolderDataData"
Const FILE_PATTERN As String = "*.xls*" ' adjust!
' The following two must be 'in sync'.
Const COMPARE_STRINGS As String = "Group,Proportion" ' add more
Const COMPARE_DELIMITER As String = ","
Dim CompareStrings() As String:
CompareStrings = Split(COMPARE_STRINGS, COMPARE_DELIMITER)
Dim UpperLimit As Long: UpperLimit = UBound(CompareStrings)
Dim fName As String: fName = Dir(FOLDER_PATH & FILE_PATTERN)
Dim i As Long, BaseName As String, IsMatch As Boolean
Do While Len(fName) > 0
' Remove the file extension.
BaseName = Left(fName, InStrRev(fName, ".") - 1)
' Loop through the compare array to determine whether it's a match.
IsMatch = False
For i = 0 To UpperLimit
If InStr(1, BaseName, CompareStrings(i), vbTextCompare) > 0 Then
IsMatch = True
Exit For
End If
Next i
' Do something if it's a match, e.g.:
If IsMatch Then
Debug.Print fName
End If
fName = Dir ' next file
Loop
End Sub
</code>
<code>Option Explicit
Sub LoopThroughFiles()
Const FOLDER_PATH As String = _
"C:UsersJay.XieDesktopSW FolderDataData"
Const FILE_PATTERN As String = "*.xls*" ' adjust!
' The following two must be 'in sync'.
Const COMPARE_STRINGS As String = "Group,Proportion" ' add more
Const COMPARE_DELIMITER As String = ","
Dim CompareStrings() As String:
CompareStrings = Split(COMPARE_STRINGS, COMPARE_DELIMITER)
Dim UpperLimit As Long: UpperLimit = UBound(CompareStrings)
Dim fName As String: fName = Dir(FOLDER_PATH & FILE_PATTERN)
Dim i As Long, BaseName As String, IsMatch As Boolean
Do While Len(fName) > 0
' Remove the file extension.
BaseName = Left(fName, InStrRev(fName, ".") - 1)
' Loop through the compare array to determine whether it's a match.
IsMatch = False
For i = 0 To UpperLimit
If InStr(1, BaseName, CompareStrings(i), vbTextCompare) > 0 Then
IsMatch = True
Exit For
End If
Next i
' Do something if it's a match, e.g.:
If IsMatch Then
Debug.Print fName
End If
fName = Dir ' next file
Loop
End Sub
</code>
Option Explicit
Sub LoopThroughFiles()
Const FOLDER_PATH As String = _
"C:UsersJay.XieDesktopSW FolderDataData"
Const FILE_PATTERN As String = "*.xls*" ' adjust!
' The following two must be 'in sync'.
Const COMPARE_STRINGS As String = "Group,Proportion" ' add more
Const COMPARE_DELIMITER As String = ","
Dim CompareStrings() As String:
CompareStrings = Split(COMPARE_STRINGS, COMPARE_DELIMITER)
Dim UpperLimit As Long: UpperLimit = UBound(CompareStrings)
Dim fName As String: fName = Dir(FOLDER_PATH & FILE_PATTERN)
Dim i As Long, BaseName As String, IsMatch As Boolean
Do While Len(fName) > 0
' Remove the file extension.
BaseName = Left(fName, InStrRev(fName, ".") - 1)
' Loop through the compare array to determine whether it's a match.
IsMatch = False
For i = 0 To UpperLimit
If InStr(1, BaseName, CompareStrings(i), vbTextCompare) > 0 Then
IsMatch = True
Exit For
End If
Next i
' Do something if it's a match, e.g.:
If IsMatch Then
Debug.Print fName
End If
fName = Dir ' next file
Loop
End Sub