By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,986 Members | 2,042 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,986 IT Pros & Developers. It's quick & easy.

Searching for a particualr word in a text file,using VBA??

P: 11

Please help me create a function that would return if a particular word exists in the given text file or not? I am working in automation testing too TestPartner which uses VBA scripting language
Apr 24 '07 #1
Share this Question
Share on Google+
7 Replies

P: 175
there's a built in function in VB and VBA called "instr"

it's format is --->
instr ( position to start search, string to search, what to search for)

so, for example, if you had a string "search for", then it would be
x = instr ( 1, "search for", "arc" )

(in your case, you would have a variable containing individual lines in the text file or the entire text file - in place of "search for" )

and x would be 3 ( VB starts at 1 instead of 0 )

if you need more, search for "instr" on the internet.

I guarantee whatever you're searching for in your document you can do it using "instr"
Apr 24 '07 #2

Expert 5K+
P: 8,434
Yes, the simplest (not necessarily best, depending on things like file size) would be to open the file in Binary mode and load it into a string in one action. Then use Instr to search it. For example, here's a very simple function which will accept a filename and a string, and tell you whether the string is found anywhere in the file...
Expand|Select|Wrap|Line Numbers
  1. Public Function StringExistsInFile(ByVal TheString As String, ByVal TheFile As String) As Boolean
  2.   Dim L As Long, S As String, FileNum As Integer
  3.   FileNum = FreeFile
  4.   Open TheFile For Binary Access Read Shared As #FileNum
  5.   L = LOF(FileNum)
  6.   S = Space$(L)
  7.   Get #1, , S
  8.   Close #FileNum
  9.   If InStr(1, S, TheString) Then
  10.     StringExistsInFile = True
  11.   End If
  12. End Function
I believe this will work (haven't tested it) but there are things to keep in mind. For example, since this reads the entire file into memory, a huge file could cause problems. You can get around this by reading the file in smaller chunks, but that coimplicates things a bit because for example the search string could start in one chunk and finish in the next.
Apr 25 '07 #3

P: 11
Thankyou for the reply!
Apr 25 '07 #4

P: 11
Thankyou! this should help.
Apr 25 '07 #5

P: 11
I have written a simple code to search fro a particular word/words in a word doc:

Expand|Select|Wrap|Line Numbers
  1. Function fnSearchWordDoc(strPath, strSrTxt)
  3. Set objWord = CreateObject("Word.Application")
  4. Set A = objWord.Documents.Open(strPath)
  6. 'MsgBox A.Content.Text
  7. Doctext = A.Content.Text
  9. If InStr(Doctext, strSrTxt) <> 0 Then
  10.    fnSearchWordDoc = "Text found in the document"
  11. Else
  12.    fnSearchWordDoc = "Text not found in the document"
  13. End If
  14. A.Close
  16. objWord.Visible = False
  17. Set objWord = Nothing
  18. End Function
And for searching in a text file:

Expand|Select|Wrap|Line Numbers
  1. Function fnFindText(strFilePath, strSrTxt)
  2.       strFileRes = fnFileExistence(strFilePath)
  3.       If strFileRes = "File Exists" Then
  4.          Const ForReading = 1
  6.          Set fso = CreateObject("Scripting.FileSystemObject")
  7.          Set f = fso.OpenTextFile(strFilePath, ForReading, True)
  8.          Do While f.AtEndOfStream <> True
  9.             A = f.readline
  10.             'MsgBox a
  11.             If InStr(A, strSrTxt) <> 0 Then
  12.                boolStFnd = "True"
  13.                fnFindText = "Text found"
  14.          Exit Do
  15.             End If
  16.          Loop
  17.          If boolStFnd <> "True" Then
  18.             fnFindText = "Text not found"
  19.          End If
  20.          f.Close
  21.       Else
  22.         fnFindText = "File does not exists"
  23.       End If
  24.     End Function
Apr 25 '07 #6

P: 1
Regarding "I believe this will work (haven't tested it)":
Function StringExistsInFile works beautifully in Excel 2007 VBA, which is exactly what I needed and where I tested it. Thanks very much -- it's brilliant!
-- Dennis
Oct 15 '08 #7

P: n/a
i like to tyy this.....thanx
Oct 18 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.