• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Loop through files, find value in column


Hello, I am trying to loop through a directory of files to get a list of file names based on a value from column A. I have a list of parts in column A (2,467 to be exact). I want to search every filename and its contents. I want to then output some listing for what was found but I am getting confused doing this entire task. My results come out blank. Hopefully this makes sense... Thanks for any help!

Column A example:
A1 = Apples
A2 = Oranges
A3 = Pears

Search files and output results of find.

File1: (Contains Apples, Oranges, Pears)
File2: (Contains Pears)
File3: (Contains Apples, Oranges)
File4: (N/A)
File5: (Contains Pears, Apples)
File6: (N/A)
File7: (Contains Oranges)

Sub StringExistsInFile()

    Dim theString
    Dim path As String
    Dim StrFile As String
    Dim fso As New FileSystemObject
    Dim file As TextStream
    Dim line As String
    Dim amount As Long
    Dim theResult As String
    Dim sHostName As String
    Dim i As Long

    path = "P:\prg\"
    StrFile = Dir(path & "*.dp")

  Do While StrFile <> ""
    Set file = fso.OpenTextFile(path & StrFile)
    Do While Not file.AtEndOfStream
        theString = Cells(Rows.Count, "A").End(xlUp).Row
        For i = 1 To Rows.Count
            line = file.ReadAllLines()
            If InStr(1, line, theString, vbTextCompare) > 0 Then
            MsgBox theString
            Userform1.ListBox1.AddItem (StrFile & ":" & theString)
            End If
        Next i
    Set file = Nothing
    Set fso = Nothing
    StrFile = Dir()
End Sub