[SOLVED] Get file path which is searched on explorer by vba excel

Issue

I would like to get file paths which are searched on explorer.

I obtain the code to searcher on the explorer with key word.

This is my code:

"Call Shell("explorer.exe " & Chr(34) & "search-ms:query=" & filename & "&crumb=location:" & filepath & Chr(34), vbNormalFocus)"

But i can not afford to get the path of the searched result.

Every who can help me to provide the code that can get the path of searched result.

Thanks for any help.

Solution

I don't think you can get the filepath returned from explorer without using some fancy windows code. But you can simulate the same thing with VBA. Here's a working code sample below. Note, it doesn't find partial mathches like search=msquery does, but that's an easy tweak. Let me know if you need that.

Function SearchForFilesInFolderTree(filename As String, filepath As String, fSearchSubFolders As Boolean) As String
Dim fso As New Scripting.FileSystemObject
Dim fld As Scripting.Folder
Dim sfld As Scripting.Folder

If fso.FileExists(filepath & filename) = True Then
    SearchForFilesInFolderTree = filepath
    Exit Function
End If

If fSearchSubFolders = True Then
    Set fld = fso.GetFolder(filepath)
    For Each sfld In fld.SubFolders
        SearchForFilesInFolderTree = SearchForFilesInFolderTree(filename, sfld.Path & "\", True)
        If SearchForFilesInFolderTree <> "" Then Exit Function
        DoEvents
    Next sfld
Else
    SearchForFilesInFolderTree = ""
End If

End Function

Here is some example code on how to call the code above:

Sub TestSearch()
Dim filename As String
Dim filepath As String
Dim FoundFolder As String
Dim fSearchSubFolders As Boolean

fSearchSubFolders = True ' set to false if you only want to search the filepath folder and not subfolders
filename = "test.xlsx"  ' this is the file you want to search for.
filepath = "c:\temp"  ' This is the root folder of your search

If Right(Trim(filepath), 1) <> "\" Then filepath = Trim(filepath) & "\"

FoundFolder = SearchForFilesInFolderTree(filename, filepath, fSearchSubFolders)
If FoundFolder <> "" Then
    MsgBox "File " & filename & " found at: " & FoundFolder
Else
    MsgBox "File " & filename & " not found in: " & filepath
End If

End Sub

Answered By - SixSigmaGuy

Answer Checked By - Gilberto Lyons (WPSolving Admin)