Wsrtjtyk

All I want to do is have the user enter a keyword in a text box and when the macro is executed; Open a new Windows' File Explorer window designated by a path

path = C:UsersMEDesktopFolder7

and Search from the variable in SearchBox1 (this is a ActiveX text box on a worksheet.)

mySearch = sht.OLEObjects("SearchBox1").Object.Text & "*" 

I've been looking all over the place and since I am relatively new to VBA Im not sure how to do this.

I saw multiple threads/posts using Shell commands to open a File Explorer Window..

 Call Shell("explorer.exe " & Chr(34) & "search-ms:query=*.pdf&crumb=location:C:UsersMEDesktopFolder7" & Chr(34), vbNormalFocus)

When I run the above line an error from the explorer stating,

'Windows cannot find ". Make sure you typed the name correctly, and then try again.'

I need the macro to search for All Files associated with the string (folder names, file names, and words/characters within each type of document(they have all been OCR'd and Indexed by Windows)) It should have the ability to search for incomplete words as well.

I've gotten the Shell to open an explorer window to the path by

Call Shell("explorer.exe " & Chr(34) & "C:UsersMEDesktopFolder7" & Chr(34), vbNormalFocus)

How can the macro search all folders and sub-folders within this freshly opened window? I don't need the results compiled into Excel or any other program. I just need to make a quick search button as if you were to manually open this folder and use the search bar.

Thanks ahead of time for any and all input!

Double Click on Cell to search

This is a solution I have combined from various places to open an explorer window at a path, that are filtered (searched) by the term in the selected cell using the windows File Explorer search function. It is triggered by a double click on a cell that contains the search term:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Not Application.Intersect(Target, Range("A1:AA1048576")) Is Nothing Then

Dim d As String

Dim searchpath As String

Dim searchlocation As String

Cancel = True

d = Selection.Value

'change window name to make sure new explorer window is opened for each instance

'copy string from manual search

searchpath = "search-ms:displayname=" & d & "%20Results%20&crumb=System.Generic.String%3A"

'copy string from manual search (e.g. my documents replace USERNAME)

searchlocation = "&crumb=location:C%3A%5CUsers%5CUSERNAME%5CDocuments"

If Not d = "" Then

    Call Shell("explorer.exe """ & searchpath & d & searchlocation & "", 1)

   'src: https://stackoverflow.com/questions/24376850/open-explorer-search-from-excel-hyperlink

End If

End If

End Sub

This opens the window in VbNormalFocus, with the window title set to the cell variable (d). The ensures that if this code is run on another cell value a new separate window will be opened. Without this I found the next time I ran the code the explorer window was not updated with the new search value, but just changed focus to the previous result.

edit: "copy from search bar" is the string after location: in the address bar of a manual search in explorer

Using ActiveX Controls

Add an ActiveX Text box (TextBox1) and button (CommandButton1) and add the following codeto the command button:

Private Sub CommandButton1_Click()

Dim d As String

Dim searchpath As String

Dim searchlocation As String

Cancel = True

d = TextBox1.Value

'change window name to make sure new explorer window is opened for each instance

'copy string from manual search

searchpath = "search-ms:displayname=" & d & "%20Results%20&crumb=System.Generic.String%3A"

'copy string from manual search (e.g. my documents replace USERNAME)

searchlocation = "&crumb=location:C%3A%5CUsers%5CUSERNAME%5CDocuments"

If Not d = "" Then

    Call Shell("explorer.exe """ & searchpath & d & searchlocation & "", 1)

   'src: https://stackoverflow.com/questions/24376850/open-explorer-search-from-excel-hyperlink

End If

End Sub

Now the user can change the text in the text box and clicking the button will open a windows file explorer search of the designated folder in the code.

Screenshot example using button search for "Editable Search Text"

This worked for me:

Sub Tester()

    ShowSearch "C:_Stufftest", "*.pdf"           'search by file name

    ShowSearch "C:_StuffMail", "reminder", True 'search by keyword

End Sub





Sub ShowSearch(searchWhere, searchFor, Optional SearchByKeyword As Boolean = False)

    Const CMD As String = "explorer.exe ""search-ms:crumb=name:{query}&crumb=location:{location}"" "

    Dim s

    s = Replace(CMD, "{query}", WorksheetFunction.EncodeURL(searchFor))

    s = Replace(s, "{location}", WorksheetFunction.EncodeURL(searchWhere))

    If SearchByKeyword Then s = Replace(s, "crumb=name:", "crumb=")

    'Debug.Print s

    Shell s

End Sub

Note: WorksheetFunction.EncodeURL() is 2013 and later. For alternatives see:

How can I URL encode a string in Excel VBA?

executing Dir() empty after a Dir() with a given path will start to list all files in that dir, you just use if InStr() <> 0 to check against your value.

sFileName = Dir(path)

Do While sFileName > ""

tmp = tmp & sFileName & ";" : sFileName = Dir()

Loop

List() = Split(tmp, ";")

there you have a list of all files inside that path, you can check sub folders the same way by going through each one doing the same thing.