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"
EDIT
You can include additional search functions with the Windows search syntax:
http://download.microsoft.com/download/8/1/7/8174a74e-3d8d-4478-abc6-84cd51ad93c4/Windows_Desktop_Advanced_Query_Reference.pdf
Eg. you can search within the folder for all files with a partial match of each word in the string by changing the search variable "d:
...
d = Selection.Value
d = "(" & Replace(d, " ", " OR ") & ")"
...
if the selection (d) had a value of Where will I find it
This will search for (Where OR will OR I OR find OR it) in windows explorer and would return files with names such as WHEREver and Last WILL and testament. I've found this useful for qualitative information, where casting a wider search is acceptable and can be easily filtered through by the user (NOTE: the above example would also return all files with a name containing i so it is not very specific!)
