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)
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)
Code:
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")
Sheets("PartList").Activate
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
Loop
file.Close
Set file = Nothing
Set fso = Nothing
StrFile = Dir()
Loop
End Sub