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!!