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

 

[taha54]

yes it does, and as i said i even copied the sentence from it and paste it in VA code

thanks

 

[hollifd]

Just to confirm,

The text that you copy from the text file and paste into the code is all caps? The search text must be in all caps since my code is searching by all capital letters.

The two places in the code where it specifies the path and filename match your actual path and filename?

This code is pretty simple and there must be something obvious to cause it not to work.

Let me know if you have checked the items above.

Thanks,

David

 

[taha54]

in the txt file, everything is in small cabs , and thats what i have in the VBA code, i changed them from big to small , could that be the problem, because both path places are correct

 

[Norie]

taha54

Try stepping through the code using F8.

David

As far as I can see your code actually takes care of the lower/upper case issue by using UCase.

ps taha54, can you post the exact code you are trying?

 

[taha54]

hi Norie, here is the exact txt file, very simple, i am trying it as a sample only for other big fles in the future

"i am glad that you are one of my people ,
but i am happy that faisal taha is one of you"

and i want to make sure that this file has "i am happy"

and if yes then put it in A(1)

 

[Norie]

taha54

Can you post the exact code, not the text?

You might have changed something in it to cause it not to work.

ps Dave's code works perfectly fine for me.

 

[taha54]

sorry Norie

Public Sub TestIt()
[A1].Select     'Start in cell A1
If Len(Dir("C:\Documents and Settings\Administrator\Desktop\faisal.txt")) > 0 Then    'replace text inside quotes with your path and filename
    Open "C:\Documents and Settings\Administrator\Desktop\faisal.txt" For Input As #1
    While Not EOF(1)
        Line Input #1, Hold$
        
        If InStr(UCase$(Hold$), "i am happy") Then
            ActiveCell.Value = Hold$
            ActiveCell.Offset(1, 0).Select
        End If
    Wend
    Close #1
Else
    MsgBox "Cannot find file."
End If

End Sub

thanks dave

 

[hollifd]

The text in the following line must be typed in all caps...

If InStr(UCase$(Hold$), "i am happy") Then

Should be...

If InStr(UCase$(Hold$), "I AM HAPPY") Then

Please try this and post back.



Norie,
Could you take a look at my other post about Workbooks.open?

Thanks,

David

 

[taha54]

it worked for the whole line....its good enough

Norie is it okay if i ask another question or will you get mad again :wink:

 

[hollifd]

If you do not want to whole line, I could modify the code to only list what you want. Can you tell me what you want or are you happy with it the way it is?

Thanks,

David