VBA打开文件资源管理器并在文本框中搜索变量

双击单元格进行搜索

这是一个解决方案,我从不同的地方,打开一个资源管理器窗口在一个路径,这是过滤(搜索)所选单元格中使用windows文件资源管理器搜索功能的条件。双击包含搜索词的单元格时会触发:

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

编辑:“从搜索栏复制”是在explorer中手动搜索的地址栏中location:之后的字符串

添加ActiveX文本框(TextBox1)和按钮(CommandButton1),并将以下代码添加到命令按钮:

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

现在用户可以更改文本框中的文本,单击该按钮将打开windows文件资源管理器搜索代码中指定的文件夹。

Screenshot example using button search for "Editable Search Text"

编辑

您可以使用Windows搜索语法包含其他搜索函数: http://download.microsoft.com/download/8/1/7/8174a74e-3d8d-4478-abc6-84cd51ad93c4/Windows_Desktop_Advanced_Query_Reference.pdf

例如,通过更改搜索变量“d:

...
d = Selection.Value
d = "(" & Replace(d, " ", " OR ") & ")"
...

如果选择(d)的值为 Where will I find it 这将搜索 (Where OR will OR I OR find OR it) WHEREver 和 Last WILL and testament . 我发现这对于定性信息很有用,在定性信息中,进行更广泛的搜索是可以接受的,并且用户可以很容易地进行筛选(注意:上面的示例还将返回名称包含 i 所以不是很具体!)

Sub Tester()
    ShowSearch "C:\_Stuff\test", "*.pdf"           'search by file name
    ShowSearch "C:\_Stuff\Mail\", "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

注意 WorksheetFunction.EncodeURL()

How can I URL encode a string in Excel VBA?

在具有给定路径的Dir()之后执行Dir()empty将开始列出该Dir中的所有文件,您只需使用if InStr()<&燃气轮机;0来检查您的值。

sFileName = Dir(path)
Do While sFileName > ""
tmp = tmp & sFileName & ";" : sFileName = Dir()
Loop
List() = Split(tmp, ";")

在那里你有一个列表,里面的所有文件的路径,你可以检查子文件夹的方式相同,通过检查每一个做同样的事情。

我不认为这是个好主意,你能做到这一点真是太棒了。我只是把这个想法更进一步,使之模块化,所以你可以添加任何类型的搜索:

Sub searchInExplorer_TEST()
    'searchInExplorer "D:\", , , True, "*.jpg", True, "24 Feb 20"
    searchInExplorer "D:\", , , , , True, "24 Feb 20", True, "picture"
End Sub


Sub searchInExplorer(searchWhere _
                    , Optional isSearchAll As Boolean, Optional strAll _
                    , Optional isSearchName As Boolean, Optional strName _
                    , Optional isSearchModified As Boolean, Optional strModified _
                    , Optional isSearchType As Boolean, Optional strType)
    
    '*****************************************************
    'https://stackoverflow.com/questions/52671500/vba-to-open-file-explorer-and-search-for-variable-in-textbox
    'ALLOWS SEARCHING IN WINDOWS EXPLORER USING VARIABLES
    'EITHER USE SEARCH ALL OR OTHER SEARCH TIMES
    'EACH SEARCH TYPE HAS AN ON/OFF SWITCH AND A STRING VARIABLE TO SEARCH BY
    '*****************************************************
        
    Dim STR As String
    
    STR = "explorer.exe ""search-ms:"
    If isSearchAll Then
        STR = STR & "crumb=:" & WorksheetFunction.EncodeURL(strAll)
        
    Else
        If isSearchName Then
            STR = STR & "&crumb=name:" & WorksheetFunction.EncodeURL(strName)
        End If
        
        If isSearchModified Then
            STR = STR & "&crumb=modified:" & WorksheetFunction.EncodeURL(strModified)
        End If
        
        If isSearchType Then
            STR = STR & "&crumb=kind:" & WorksheetFunction.EncodeURL(strType)
        End If
        
    End If
    
    STR = STR & "&crumb=location:" & WorksheetFunction.EncodeURL(searchWhere)
    STR = STR & """ "
    
    Debug.Print STR
    Shell STR
End Sub