Mikael
10-28-2019, 01:51 PM
Hello,
I have the below code (found on this website but it won't let me post if I link to it (?53031-Search-String-in-Text-Files) and am trying to modify it to the do following:
I have strings to search in E3:E7. I have the directory path of many text files located in cell I1. I want it to search all text files in the path in I1 and search in them for the string located in E3. If it finds a match, I want it returned starting at I3. If no match, it should say "Nothing Found"
Then it goes to the next string in E4 and repeats.
This is what I have.
Sub Button1_Click()
Dim theString As String
Dim path As String
Dim StrFile, NewFile As String
Dim fso As New FileSystemObject
Dim file As TextStream
Dim line As String
Dim Result As Range
Dim Data As Range
Set Data = Range("E3")
theString = Data.Value
path = Range("I1").Value
StrFile = Dir(path & "*.txt")
Set Result = Range("I3")
Do While StrFile <> ""
theString = Data.Value
Set file = fso.OpenTextFile(path & StrFile)
Do While Not file.AtEndOfLine
line = file.ReadLine
If InStr(1, line, theString, vbTextCompare) > 0 Then
Result.Value = StrFile
Result = Result.Offset(0, 1)
Data = Data.Offset(0, 1)
Exit Do
Else
Result.Value = "Nothing Found"
Result = Result.Offset(0, 1)
Data = Data.Offset(0, 1)
End If
Loop
file.Close
Set file = Nothing
Set fso = Nothing
StrFile = Dir()
Loop
End Sub
I have the below code (found on this website but it won't let me post if I link to it (?53031-Search-String-in-Text-Files) and am trying to modify it to the do following:
I have strings to search in E3:E7. I have the directory path of many text files located in cell I1. I want it to search all text files in the path in I1 and search in them for the string located in E3. If it finds a match, I want it returned starting at I3. If no match, it should say "Nothing Found"
Then it goes to the next string in E4 and repeats.
This is what I have.
Sub Button1_Click()
Dim theString As String
Dim path As String
Dim StrFile, NewFile As String
Dim fso As New FileSystemObject
Dim file As TextStream
Dim line As String
Dim Result As Range
Dim Data As Range
Set Data = Range("E3")
theString = Data.Value
path = Range("I1").Value
StrFile = Dir(path & "*.txt")
Set Result = Range("I3")
Do While StrFile <> ""
theString = Data.Value
Set file = fso.OpenTextFile(path & StrFile)
Do While Not file.AtEndOfLine
line = file.ReadLine
If InStr(1, line, theString, vbTextCompare) > 0 Then
Result.Value = StrFile
Result = Result.Offset(0, 1)
Data = Data.Offset(0, 1)
Exit Do
Else
Result.Value = "Nothing Found"
Result = Result.Offset(0, 1)
Data = Data.Offset(0, 1)
End If
Loop
file.Close
Set file = Nothing
Set fso = Nothing
StrFile = Dir()
Loop
End Sub