(Solved) – Display search text in text file with VBA Excel


I am new to VB and have a problem. I hope someone can help me to solve it

I have a text file named data.txt
It has 1 lines in it


I write a script which reads this text file and look for the string such as “stutent” and print all the “student” we can found in cell in excel (B1,C1,D1….). In this example we have 5 “student”. It will display in cell B1,C1,D1,E1,F1 in sheet.
I tried till this point but it just give me only one “student” not five.

Please someone help me.

Sub SearchTextFile()
Const strFileName = "C:data.txt"
Const strSearch = "student"
Dim strLine As String
Dim f As Integer
Dim lngLine As Long
Dim blnFound As Boolean
Dim lPosition As Long
f = FreeFile
Open strFileName For Input As #f
Do While Not EOF(f)
    lngLine = lngLine   1
    Line Input #f, strLine
    If InStr(1, strLine, strSearch, vbBinaryCompare) > 0 Then
        blnFound = True
        lPosition = InStr(1, strLine, strSearch, vbTextCompare)            
        MsgBox "Search string found" & strSearch, vbInformation
        Exit Do
    End If
Close #f
If Not blnFound Then
    MsgBox "Search string not found", vbInformation
End If

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *