search a text file

[taha54]

hi all,
i was not sure if it is possible in Excel VBA to open a text file and search for a specifc word so lets say i have a 3 pages text file with english word "temp.txt", and i am looking for the words "i am happy" then take these words and store them in any excel file called " temp2.xsl"


let me know if we can do that, and if not what do you suggest

thanks

 

[Norie]

Get mad again.

Who me?

Go ahead and ask, but if it's not related to this thread then create a new one.

 

[taha54]

okay, i really appreciate your help,

lets say i have this line "my name is faisal i am happy to meet hollfid and thank you"

now that we found i am happy, can we copy all words after it until we reach "and thank you" then paste these words in A

so i want everything between "i am happy" and "and thank you" to be placed in A

really sorry to bother you
and thanks alot

 

[Norie]

Something like this perhaps?

Public Sub TestIt()
Dim pos1 As Long
Dim pos2 As Long
Dim strStart As String
Dim strFinish As String

strStart = "I AM HAPPY"
strFinish = "AND THANK YOU"

If Len(Dir("C:\Temp\Temp.txt")) > 0 Then    'replace text inside quotes with your path and filename
    Open "C:\Temp\Temp.txt" For Input As #1
    While Not EOF(1)
        Line Input #1, hold$
        pos1 = InStr(UCase$(hold$), strStart)
        If pos1 Then
            Range("A1") = Mid(hold$, pos1 + Len(strStart) + 1)
            pos2 = InStr(UCase(Range("A1")), strFinish)
            Range("A1") = Left(Range("A1"), pos2 - 1)
        End If
    Wend
    Close #1
Else
    MsgBox "Cannot find file."
End If
End Sub

 

[taha54]

so to make sure, strStart is the "i am happy" and strFinish " and thank you"

right norie?

thanks alot for your help

 

[hollifd]

Try this...

Public Sub TestIt()

[A1].Select     'Start in cell A1
If Len(Dir("C:\Temp\Temp.txt")) > 0 Then    'replace text inside quotes with your path and filename
    Open "C:\Temp\Temp.txt" For Input As #1
    While Not EOF(1)
        Line Input #1, Hold$
        
        Let Found1 = InStr(UCase$(Hold$), "I AM HAPPY")
        If Found1 > 0 Then
            Let MyInfo$ = Mid$(Hold$, Found1 + 10)
            Let Found2 = InStr(UCase$(MyInfo$), "THANK YOU")
            If Found2 > 0 Then
                MyInfo$ = Trim$(Mid$(MyInfo$, 1, Found2 - 1))
                ActiveCell.Value = MyInfo$
                ActiveCell.Offset(1, 0).Select
            End If
        End If
    Wend
    Close #1
Else
    MsgBox "Cannot find file."
End If

End Sub

I have to leave work now. I hope this works for you. If not, post back and I will have another look at it tomorrow.

Thanks and good luck,

David

 

[taha54]

thanks alot Norie and David

you are really great