Assistance with bringing specific items from text file into Excel

[ajb1965]

Hello all,

I'm a complete rookie at VBA and macros so I apologize if this is rudimentary stuff. I searched Google and then the forums here and couldn't find what I was looking for.

I am trying to extract specific information from a text file and place it in three Excel columns horizontally so that I can then import it into Access as a table. Via Google I was able to get the three specific fields into excel the way I want it, but it only finds the first instance of each field then stops. I want it to scour the entire file and fill the cells with the data.

The fields I'm looking for in the file will have additional data to the right of the field name, and that data can be different throughout the file.

I've attached a sample of the raw text and the spreadsheet. The text files can get upwards of 2meg so they contain a lot of data.

If you could give me a few pointers or send me down the correct path towards where I can learn to do what I need to do I'd greatly appreciate it.

Cheers,

AJB

[Paul_Hossler]

Assumes that all 3 field types are in each block of data

As a thought, I added an optional format you can consider

Private Sub CommandButton1_Click()
    Dim myFile As String, text As String, textline As String, FileNum As Long
    
    Dim outRow As Long
    
    outRow = 1
    
    myFile = Application.GetOpenFilename()
    
    FileNum = FreeFile
    Open myFile For Input As #FileNum
    
    While Not EOF(FileNum)
        Line Input #FileNum, textline
            
        If Left(textline, 4) = "LEN:" Then
            ActiveSheet.Cells(outRow, 1).Value = Trim(textline)
        ElseIf Left(textline, 8) = "SIP_URI:" Then
            ActiveSheet.Cells(outRow, 2).Value = Trim(textline)
        ElseIf Left(textline, 14) = "SIP_SUBDOMAIN:" Then
            ActiveSheet.Cells(outRow, 3).Value = Trim(textline)
            outRow = outRow + 1
        End If
    
    Wend
    
    Close #FileNum
End Sub

[ajb1965]

Oh my gosh it was that simple? You really are a wizard. This is perfect thank you very very much! I'm going to take a deeper dive into what you did here and hopefully I can apply it to more fields for another project I'm working on.

Amazing, thank you!!