VBA/MACRO : read next line from text file after a matching string

Question

I need to read a text file then search for a string and read next line or line right before it.

example text file:
..........

Information for the Online Portal

Primary Contact Full

John Doe

Phone Number

0000000000

[email protected]

E-Mail

Sites Ordering For

..........

I need to search for "Primary Contact Full" and get the name value "John Doe" (which is dynamic)

similarly, I need to search for "E-Mail" to retrieve the vale "[email protected]"

My Sample code to read so far is :

Sub ExtractData()

Dim myFile As String, text As String, textline As String, posLat As Integer, posLong As Integer

myFile = Application.GetOpenFilename()
Open myFile For Input As #iFile

Do Until EOF(1)
    Line Input #1, textline
        text = text & textline
        posLat = InStr(text, "Primary Contact Full")
Loop
Close #1

Range("A1").Value = Mid(text, posLat + 55, 25)

End Sub

I need to be able to read till end of line is encountered.

What is wrong with the sample code currently? Is there an error? Does it give the wrong result? — Qwertie
– Qwertie, Commented Mar 27, 2018 at 2:59

Kevin · Accepted Answer · 2018-03-27 16:12:39Z

For the 1st case you can simply call Line Input to read the next line when the condition is met. For the second case keep track of the previous line and use that if the other conditins is met. There is an example loop below (without error handling which you may need.)

Another option would be to read the whole thing in as an array, the loop through the array.

    Dim myFile As String, text As String, textline As String, posLat As Integer, posLong As Integer, prevLine

    Open myFile For Input As #1

    Do Until EOF(1)
        Line Input #1, textline

            posLat = InStr(textline, "Primary Contact Full")

            If (posLat > 0) Then
                'this skips a line
                Line Input #1, textline
                'do something with text line here
            End If

            posLat = InStr(textline, "E-Mail")

            If (posLat > 0) Then
                'do something with prev line here
            End If

            prevLine = textline
    Loop
    Close #1

Collectives™ on Stack Overflow

VBA/MACRO : read next line from text file after a matching string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related