Hello everyone
The following masterpiece of code is Mr. Leith Ross's code. It searches for specific file names that contain specific string
Private FileFilter As String
Function ListFiles(ByVal FolderPath As Variant, ByRef OutputCell As Range, Optional ByVal SearchDepth As Long)
' Written: November 07, 2015
' Author: Leith Ross
' SearchDepth = Maximum level (depth) of Subfolders to search.
Dim N As Long
Dim oFile As Object
Dim oFiles As Object
Dim oFolder As Variant
Dim oShell As Object
If oShell Is Nothing Then
Set oShell = CreateObject("Shell.Application")
End If
Set oFolder = oShell.Namespace(FolderPath)
If oFolder Is Nothing Then
MsgBox "The Folder '" & FolderPath & "' Does Not Exist.", vbCritical
SearchDepth = 0
Exit Function
End If
Set oFiles = oFolder.Items
N = 0
oFiles.Filter 64, FileFilter
For Each oFile In oFiles
OutputCell.Offset(N, 0) = oFolder.self.Name
OutputCell.Parent.Hyperlinks.Add OutputCell.Offset(N, 1), oFile.Path, , , oFile.Name
N = N + 1
Next oFile
Set OutputCell = OutputCell.Offset(N, 0)
oFiles.Filter 32, "*"
If SearchDepth <> 0 Then
For Each oFolder In oFiles
Call ListFiles(oFolder, OutputCell, SearchDepth - 1)
Next oFolder
End If
End Function
Sub ListFilesTest()
Dim lastRow As Long
Dim Rng As Range
Dim Wks As Worksheet
Set Wks = Worksheets("Sheet1")
Set Rng = Wks.Range("A1:B1")
lastRow = Wks.Cells(Rows.Count, "A").End(xlUp).Row
FileFilter = InputBox("Enter the Full or Partial name of the Files to Find.")
If FileFilter = "" Then Exit Sub
FileFilter = "*" & FileFilter & "*"
Rng.Resize(lastRow - Rng.Row + 1, 2).Clear
ListFiles "G:\Test", Worksheets("Sheet1").Range("A1"), 1
End Sub
It works fine but I need to add flexible way to be able to search for more than a string ..
for example if I have a file name "Copy Non-Contiguous Columns To Another Sheet In Different Order"
If I searched the string 'non' it is ok and the file name appears in results
If I searched the string 'order' it is ok and the file name appears in results
But if I searched 'non order' the file name doesn't appear in results ...!!and this is the problem for me
I mean it is ok if I used one string
I need it to be flexible that I need sometimes to type two ot three or more strings ..
So as in example I need to the file name "Copy Non-Contiguous Columns To Another Sheet In Different Order" to appear in results if I typed in inputbox the string 'order non copy' .. the order of strings may be from right to left or from left to right or scrambled ..and the case of letters is not sensitive
Thanks advanced for help
Bookmarks