search a text file

T

taha54

Board Regular
Joined
Aug 11, 2006
Messages
103
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
See if this code gives you any ideas...


Code: 
[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$
        
        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

Good luck!

David
 
Upvote 0
thanks alot for your help , but the code did not do anything, unless i could not understand it, can you explain it please

thanks alot
 
Upvote 0
Sure,

Open a New / Blank spreadsheet.

Place the code into a module by...
Go to Tools, Macro, Visual Basic Editor
Click Insert, Module

Paste this code into screen.

Code: 
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$ 
        
        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


Run the code by...

Tools, Macro, Macros
Click TestIt

The Macro will run. Here is what it will do...


The first thing the code does is position your cursor in cell A1
Next, the code checks to see if there is a text file in C:\Temp called Temp.txt.

If the file exists, the code opens the text file
It reads each line in the text file (until the end of the file is reached) and searches for the text "I AM HAPPY"
If the text is found, then the code writes the line of the text file into cell A1 and moves the cursor to the next row in your spreadsheet
If the text "I AM HAPPY" is not found, the code reads the next line from the text file
If the file does not exist, the code shows a message box telling you that the file cannot be found

Does that help you solve your problem?

Thanks,

David
 
Upvote 0
there is no errors, but there is nothing being displayed even though the sentence is in the file
 
Upvote 0
Can you open the file with a text editor (Like Notepad.exe) and read the text in the file? Have you typed the search text exactly as it appears in the text file? BE SURE TO TYPE - "I AM HAPPY" or whatever text you are searching for in all caps.
Is the text file too long to post in this forum?

I just checked the code that I posted and it works for me.

When you run the macro, do you get any messages?

Just trying to figure this out for you.

David
 
Upvote 0
no i get no messages , and i checked the txt file and copy the sentece from it and paste it to the VBA code, i am not getting any errors but there is nothing recorded on the excel page after runing the code
 
Upvote 0
Do you actually have a text file called Temp.txt in a directory called Temp on your C: drive?

Does it actually contain the exact phrase 'I AM HAPPY"?
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Automate data export from multiple word tables into excel
Replies
8
Views
310
NdNoviceHlp
NdNoviceHlp
M
  • Solved
Word Header Text
Replies
1
Views
163
RoryA
RoryA
C
  • Solved
Highlight entire rows in used range where any cell contains searched text
Replies
5
Views
692
CorrieAnn
C
A
  • Question Question
Error 5854 - String Parameter Too Long
Replies
2
Views
639
Artik
Artik
M
  • Question Question
Need help saving an already open pdf file using name from spreadsheet through VBA
Replies
6
Views
283
MatthewBiersay
M

Forum statistics

Threads
1,225,327
Messages
6,184,300
Members
453,227
Latest member
Slainte

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top