PDA

View Full Version : Search for a string in text files.



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

SamT
10-28-2019, 02:20 PM
.Offset(0, 1) means the next Column
.Offset(1) and .Offset(1, 0) mean the next Row

大灰狼1976
10-29-2019, 12:10 AM
Hi Mikael.
Welcome to vbax forum.
maybe:

Set Result = Result.Offset(1)
Set Data = Data.Offset(1)

Mikael
10-29-2019, 05:53 AM
Hi Mikael.
Welcome to vbax forum.
maybe:

Set Result = Result.Offset(1)
Set Data = Data.Offset(1)


Thank you both for catching my rookie mistake haha. I made the change, it flashes "Nothing Found" several times in the appropriate cell then throws an automation exception.