CODEWITHSUNDEEP
vbaexcel
Luudski
Luudski

Reputation: 11

Excel - search keyword in TXT file and write line where keyword is found to an excel cell

I would like to search with a keyword in a TXT file and write the line where keyword is found to an excel cell.

I have a piece of VBA code that works, but it only writes the keyword + the characters i give extra to it. So i'll exacly have to know how many characters there are after the keyword.

My question is can someone help me with the code that it can write the complete line.. so to end of line..

I'll need:

Thanks!!

Private Sub CommandButton1_Click()

Dim myFile As String
Dim text As String
Dim textLine As String

Dim S_Name As Integer

myFile = Application.GetOpenFilename()

Open myFile For Input As #1

Do Until EOF(1)
    Line Input #1, textLine
    text = text & textLine
Loop

Close #1

S_Name = InStr(text, "keyword1")

Range("A10").Value = Mid(textLine, S_Name + 0, 30)

'save sheet to txt file
ActiveWorkbook.SaveAs Filename:="c:" & "keyword_file " + Format(Now(), "YYYYMMDD") _
& ".txt", FileFormat:=xlTextWindows

End Sub

Upvotes: 1

Views: 614

Answers (1)

Domenic
Domenic

Reputation: 8104

When looping through each line, test the line for the keyword, and only assign your variable text the line that meets the criteria, and then exit the loop immediately...

Open myFile For Input As #1

text = ""
Do Until EOF(1)
    Line Input #1, textLine
    If InStr(1, textLine, "keyword1") > 0 Then
        text = textLine
        Exit Do
    End If
Loop

Close #1

Range("A10").Value = text

When a line feed (chr(10) or vbLf) is used to mark the end of the line, the FileSystemObject can be used instead...

'
'
'

Dim oFSO As Object
Dim oTS As Object

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oTS = oFSO.OpenTextFile(myFile)

text = ""
Do Until oTS.AtEndOfStream
    textLine = oTS.ReadLine
    If InStr(1, textLine, "keyword1") > 0 Then
        text = textLine
        Exit Do
    End If
Loop

oTS.Close

Range("A10").Value = text

'etc
'
'

Set oFSO = Nothing
Set oTS = Nothing

Upvotes: 1

Related Questions