Need to fetch particular string from text file into Excel sheet

Question

I need to find a key value pair from text file and paste it in Excel column

There are multiple occurrence of the key value pair till end of file

• Key = Response Code which is constant
• Value = not constant changes every time

Example:

Text file :

Username : admin 
Old password : qqqq
New password : 1111
Security question : 1
Security answer : Mom

Response Code: -500

Operation Completed

Response Code: -100
....
Response Code: -202
....
....

My code :

    Dim myFile As String
    Dim text As String
    Dim textline As String
    Dim x As Integer

    myFile = "C:\test\test.log"
    Open myFile For Input As #1
    Do Until EOF(1)
      Line Input #1, textline
      text = text & textline
    Loop
    Close #1
    x = InStr(text, "Response code")
    Range("A1").Value = Mid(text, x + 15, 3)

Note: I am getting only the first occurrence i.e Response Code:-500 I want loop that will find all occurrence till end of file and paste that content in Excel sheet column A1.

Answer 1

Small mods to your code:

Sub dural()
    Dim myFile As String
    Dim text As String
    Dim textline As String
    Dim i As Long

    myFile = "C:\TestFolder\test.log"
    Close #1
    Open myFile For Input As #1

    i = 1
    Do Until EOF(1)
        Line Input #1, textline
        If InStr(textline, "Response Code:-") > 0 Then
            Cells(i, 1).Value = Replace(textline, "Response Code:-", "")
            i = i + 1
        End If
    Loop
    Close #1
End Sub

produce: