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.

http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops then sculpt your regex to look for "Response Code" — Doug Coats, Nov 16 '16 at 14:35
to add on to Dougs comment, the regex would look something like: `(Response Code:?.\d+)` if I'm not completely off. — Andreas, Nov 16 '16 at 14:45

score 1 · Answer 1 · answered Nov 16 '16 at 14:59

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:

answered Nov 16 '16 at 14:59

Gary's Student

95,722
10
59
99

Thanks a lot you made my day :) .. can you please explain me Cells(i, 1).Value = Replace(textline, "Response Code:-", "") this line in your code ???? – Nov 17 '16 at 07:05

Need to fetch particular string from text file into Excel sheet

1 Answers1