How do I find a specific string in one of many text .log files using VBA?
Solution 1
This code:
looks for all
*.log
file extensionsC:\MyDownloads\
opens each
*.log
file and reads each lineif
theString
MAGIC is found, then it prints the file name in theImmediate Widnow
(CTRL+G)
Sub StringExistsInFile()
Dim theString As String
Dim path As String
Dim StrFile As String
Dim fso As New FileSystemObject
Dim file As TextStream
Dim line As String
theString = "MAGIC"
path = "C:\MyDownloads\*.log"
StrFile = Dir(path & "*.log")
Do While StrFile <> ""
'Find TheString in the file
'If found, debug.print and exit loop
Set file = fso.OpenTextFile(path & StrFile)
Do While Not file.AtEndOfLine
line = file.ReadLine
If InStr(1, line, theString, vbTextCompare) > 0 Then
Debug.Print StrFile
Exit Do
End If
Loop
file.Close
Set file = Nothing
Set fso = Nothing
StrFile = Dir()
Loop
End Sub
Solution 2
Application.FileSearch
was removed in 2007+ versions of Excel. A while back, I found this function which replicates it. I have used it on occasion, but ordinarily I think I just use FileSystemObject
or Dir
.
Sub FileSearch()
'
' Example of FileSearchByHavrda procedure calling as replacement of missing FileSearch function in the newest MS Office VBA
' 01.06.2009, Author: P. Havrda, Czech Republic
'
Dim sDir As String
sDir = Range("K3").Value
Dim FileNameWithPath As Variant
Dim ListOfFilenamesWithParh As New Collection ' create a collection of filenames
Dim rCount As Long 'row counter
' Filling a collection of filenames (search Excel files including subdirectories)
Call FileSearchByHavrda(ListOfFilenamesWithParh, sDir, "*.xls", False)
' Print list to immediate debug window and as a message window
For Each FileNameWithPath In ListOfFilenamesWithParh ' cycle for list(collection) processing
Debug.Print FileNameWithPath & Chr(13)
'MsgBox FileNameWithPath & Chr(13)
rCount = Application.WorksheetFunction.CountA(Range("A:A")) + 1
ActiveSheet.Cells(rCount, 1).Value = FileNameWithPath
Next FileNameWithPath
' Print to immediate debug window and message if no file was found
If ListOfFilenamesWithParh.Count = 0 Then
Debug.Print "No file was found !"
MsgBox "No file was found !"
End If
End Sub
'//------------------------------------------------------------------------------------------------
Private Sub FileSearchByHavrda(pFoundFiles As Collection, pPath As String, pMask As String, pIncludeSubdirectories As Boolean)
'
' Search files in Path and create FoundFiles list(collection) of file names(path included) accordant with Mask (search in subdirectories if enabled)
' 01.06.2009, Author: P. Havrda, Czech Republic
'
Dim DirFile As String
Dim CollectionItem As Variant
Dim SubDirCollection As New Collection
' Add backslash at the end of path if not present
pPath = Trim(pPath)
If Right(pPath, 1) <> "\" Then pPath = pPath & "\"
' Searching files accordant with mask
DirFile = Dir(pPath & pMask)
Do While DirFile <> ""
pFoundFiles.Add pPath & DirFile 'add file name to list(collection)
DirFile = Dir ' next file
Loop
' Procedure exiting if searching in subdirectories isn't enabled
If Not pIncludeSubdirectories Then Exit Sub
' Searching for subdirectories in path
DirFile = Dir(pPath & "*", vbDirectory)
Do While DirFile <> ""
' Add subdirectory to local list(collection) of subdirectories in path
If DirFile <> "." And DirFile <> ".." Then If ((GetAttr(pPath & DirFile) And vbDirectory) = 16) Then SubDirCollection.Add pPath & DirFile
DirFile = Dir 'next file
Loop
' Subdirectories list(collection) processing
For Each CollectionItem In SubDirCollection
Call FileSearchByHavrda(pFoundFiles, CStr(CollectionItem), pMask, pIncludeSubdirectories) ' Recursive procedure call
Next
End Sub
Kairan
Updated on March 30, 2020Comments
-
Kairan about 4 years
This is the code I have so far to find all the log files in a folder. But I need to be able to find a specific string in each file, if it is found in one file, stop looking and exit the loop and report back what filename it was in.
There seems to be so many different ways to open a file and search it that I do not know which is the best and I do not typically use VBA but it is all I have access to at the moment.
On a side note, there would be a max of 36 log files and each file max of 5MB each.
Sub StringExistsInFile() Dim TheString As String TheString = "MAGIC" Dim StrFile As String StrFile = Dir("c:\MyDownloads\*.log") Do While Len(StrFile) > 0 'Find TheString in the file 'If found, debug.print and exit loop Loop End Sub
I had found this code but seems in 2007+ versions of Excel VBA Application.FileSearch was eliminated:
Sub FindText() 'http://www.mrexcel.com/forum/excel-questions/68673-text-file-search-excel-visual-basic-applications.html Dim i As Integer 'Search criteria With Application.FileSearch .LookIn = "c:\MyDownloads" 'path to look in .FileType = msoFileTypeAllFiles .SearchSubFolders = False .TextOrProperty = "*MAGIC*" 'Word to find in this line .Execute 'start search 'This loop will bring up a message box with the name of 'each file that meets the search criteria For i = 1 To .FoundFiles.Count MsgBox .FoundFiles(i) Next i End With End Sub
-
Zev Spitz over 8 yearsThat's all I have at the moment. WSH+Javascript? Powershell?
-
Zev Spitz over 8 yearsIf you need more flexibility than a single string, consider using regular expressions.
-
-
David Zemens almost 11 yearsCould you just use
If Instr(1, file.ReadAll, theString, vbTextCompare) > 0 Then Debug.Print StrFile
instead of theDo While
loop? -
Admin almost 11 years@DavidZemens im going to try that later but i think you are right, it would have been more efficient in a way. good point David
-
psiphi75 about 8 yearsI had to add the "Microsoft Scripting Runtime" to get access to
FileSystemObject
. See: stackoverflow.com/questions/3233203/… -
Chito about 7 yearsI think
path = "C:\MyDownloads\*.log"
should bepath = "C:\MyDownloads\"