Extract file names from a File Explorer search into Excel

Assuming the location is indexed you can access the catalog directly with ADO (add a reference to Microsoft ActiveX Data Objects 2.x):

Dim cn  As New ADODB.Connection
Dim rs  As New ADODB.Recordset
Dim sql As String

cn.Open "Provider=Search.CollatorDSO;Extended Properties='Application=Windows'"

sql = "SELECT System.ItemNameDisplay, System.ItemPathDisplay FROM SystemIndex WHERE SCOPE='file:C:\look\here' AND System.Kind <> 'folder' AND CONTAINS(System.FileName, '""*.PDF""') AND CONTAINS ('""find this text""')"

rs.Open sql, cn, adOpenForwardOnly, adLockReadOnly

If Not rs.EOF Then
    Do While Not rs.EOF
        Debug.Print "File: "; rs.Collect(0)
        Debug.Print "Path: "; rs.Collect(1)
        rs.MoveNext
    Loop
End If

Try using the next function, please:

Function GetFilteredFiles(foldPath As String) As Collection
    'If using a reference to `Microsoft Internet Controls (ShDocVW.dll)_____________________
    'uncomment the next 2 lines and comment the following three (without any reference part)
    'Dim ExpWin As SHDocVw.ShellWindows, CurrWin As SHDocVw.InternetExplorer
    'Set ExpWin = New SHDocVw.ShellWindows
    '_______________________________________________________________________________________
    'Without any reference:_____________________________________
    Dim ExpWin As Object, CurrWin As Object, objshell As Object
    Set objshell = CreateObject("Shell.Application")
    Set ExpWin = objshell.Windows
    '___________________________________________________________
    Dim Result As New Collection, oFolderItems As Object, i As Long

    Dim CurrSelFile As String
    For Each CurrWin In ExpWin
        If Not CurrWin.Document Is Nothing Then
            If Not CurrWin.Document.FocusedItem Is Nothing Then
                If left(CurrWin.Document.FocusedItem.Path, _
                    InStrRev(CurrWin.Document.FocusedItem.Path, "\")) = foldPath Then
                
                    Set oFolderItems = CurrWin.Document.folder.Items
                    For i = 0 To oFolderItems.count
                        On Error Resume Next
                          If Err.Number <> 0 Then
                             Err.Clear: On Error GoTo 0
                          Else
                             Result.Add oFolderItems.item(CLng(i)).Name
                             On Error GoTo 0
                          End If
                    Next
                End If
            End If
        End If
    Next CurrWin
    Set GetFilteredFiles = Result
End Function

Like it is, the function works without any reference...

The above function must be called after you executed the search query in your existing code. It can be called in the next (testing) way:

Sub testGetFilteredFiles()
  Dim C As Collection, El As Variant
  Set C = GetFilteredFiles("C:\Teste VBA Excel\")'use here the folder path you used for searching
  For Each El In C
    Debug.Print El
  Next
End Sub

The above solution iterates between all IExplorer windows and return what is visible there (after filtering) for the folder you initially used to search.

You can manually test it, searching for something in a specific folder and then call the function with that specific folder path as argument ("\" backslash at the end...).

I've forgotten everything I ever knew about VBA, but recently stumbled across an easy way to execute Explorer searches using the Shell.Application COM object. My code is PowerShell, but the COM objects & methods are what's critical. Surely someone here can translate.

This has what I think are several advantages:

1. The query text is identical to what you wouold type in the Search Bar in Explorer, e.g.'Ext:pdf Content:compressor'

2. It's easily launched from code and results are easily extracted with code, but SearchResults window is available for visual inspection/review.

3. With looping & pauses, you can execute a series of searches in the same window.

I think this ability has been sitting there forever, but the MS documentation of the Document object & FilterView method make no mention of how they apply to File Explorer.

I hope others find this useful.

$FolderToSearch = 'c:\Path\To\Folder'
$SearchBoxText  = 'ext:pdf Content:compressor'

$Shell = New-Object -ComObject shell.application

###  Get handles of currenlty open Explorer Windows
$CurrentWindows = ( $Shell.Windows() | Where FullName -match 'explorer.exe$' ).HWND

$WinCount = $Shell.Windows().Count
$Shell.Open( $FolderToSearch )

Do { Sleep -m 50 } Until ( $Shell.Windows().Count -gt $WinCount )

$WindowToSerch = ( $Shell.Windows() | Where FullName -match 'explorer.exe$' ) | Where { $_.HWND -notIn $CurrentWindows }

$WindowToSearch.Document.FilterView( $SearchBoxText )

Do { Sleep -m 50 } Until ( $WindowToSearch.ReadyState -eq 4 )

### Fully-qualified name:
$FoundFiles = ( $WindowToSearch.Document.Folder.Items() ).Path
### or just the filename:
$FoundFiles = ( $WindowToSearch.Document.Folder.Items() ).Name

### $FoundFIles is an array of strings containing the names.
### The Excel portion I leave to you! :D