Search through folder / files with excel vba

Question

The goal is to create a macro, that takes whatever input string and searches the specified directory, and the files in that directory for that string. Absolute requirement is, that not only the file names are checked but also the content of the files. Output should be a list of the results. What i've got so far is a macro that looks like this:

Sub Test()

    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"

    searchlocation = "&crumb=location:C%3A%5CUsers%5Csturm%5COneDrive%5CVigiles Capital GmbH%5C01 Vigiles Capital GmbH Team%5C09 Consulting%5C01 SRW%5C01 Peergroup Vergleich%5C05 SR am Westpark%5CDaten%5CZusatz 1"
        If Not d = "" Then
            Call Shell("explorer.exe """ & searchpath & d & searchlocation & "", 1)
        End If

End Sub

This opens the windows explorer with the results of my search. What i need now is to put the results i get from this operation into a list, and then close the window again.

Absolute world class would be a solution, which doesnt even require the call shell, i.e. the same operation without opening the explorer window.

If its possible, how can i make it work?

Answer 1

What kind of files are you trying to search through? other excel files? CSV? ppt? word? The below code is a modified version of something I've recently finished and have been using. Assuming the files you're searching through are .csv something like the below code should work. Although you might have to parse the filename or use '*' in the searchstring. This isn't tested but it's easily modified for other file types.

Sub SearchDir()

Dim wb as Workbook
Dim ws as Worksheet
Dim filepath as String
Dim filename as String
Dim rng as Range
Dim i as Variant
Dim results as Worksheet
Dim resultslr as long
Dim lastrow as long
Dim searchString as string

Set results = ThisWorkbook.Worksheet("results")

filePath = "C:\Local_Path"
fileName = Dir(filePath & "*.csv")

searchString = "what you're searching for"

Do While fileName <> ""

    If fileName like searchString then
       resultslr = results.Cells(Rows.count, "a").End(xlUp).Row + 1
       results.Cells(resultslr, "a").Resize(1, 1).Value = fileName
     End if

    Set wb = Excel.Workbooks.Open(filePath & fileName)   'opens the file
    Set ws = wb.Worksheets(1)      'sets the worksheet within the csv

    lastrow = ws.Cells(Rows.count, "a").End(xlUp).Row
    Set rng = Range("A2:someEndColumn" & lastrow)   'replace someEndColumn with however your dat ais arranged.

    For each i in rng                                   'searches each cell in the range for your seachString and puts the results in a list on worksheet 'results'
       if i.value Like searchString then
          resultslr = results.Cells(Rows.count, "a").End(xlUp).Row + 1
          results.Cells(resultslr, "a").Resize(1, 1).Value = i.Value
       End if
     Next i    

    wb.Close False               'closes the file

    fileName = Dir                'next file in directory
Loop
`more code for other stuff
end sub

Answer 2

This link outlines how to use SQL to query the Windows Search index.

http://www.thejoyofcode.com/Using_Windows_Search_in_your_applications.aspx

The code there is C# but the basic approach is translatable to VBA/ADO.

Here's an example:

Sub Tester()
    
    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordset = CreateObject("ADODB.Recordset")
    
    objConnection.Open "Provider=Search.CollatorDSO;" & _
                       "Extended Properties='Application=Windows';"
    
    objRecordset.Open "SELECT System.ItemName, System.ItemFolderPathDisplay " & _
                      " FROM SystemIndex" & _
                      " WHERE SCOPE = 'file:C:/_Stuff/local files' " & _
                      " and contains('[email protected]')", objConnection
        
    If Not objRecordset.EOF Then
        objRecordset.MoveFirst
        Do Until objRecordset.EOF
            With objRecordset.Fields
                Debug.Print .Item("System.ItemName"), _
                            .Item("System.ItemFolderPathDisplay")
            End With
            objRecordset.MoveNext
        Loop
    Else
        Debug.Print "no records found"
    End If
    
    objRecordset.Close
    Set objRecordset = Nothing
    objConnection.Close
    Set objConnection = Nothing

End Sub