Text file search in Excel VBA

[Crespo25]

Hi guys,

Really sorry for asking this question again but I've been stuck on this since Friday and I'm really keen to know the answer :

The mini project I've been working on does pretty much what I want it to do. It takes a list of script names and by providig it with the scripts folder/server and database the scripts are run and the log files are dumped in a newly created folder named logs.

Now that I have established how to check for the existence of a folder and creating new folders in Excel VBA, the next step is to check for the existence of a particular text file and if it exists, search the contents of that file for a specific word or sentence.

I need to know which objects and methods provide such capability [if any]

Any ideas?

Thanks in advance.

 

[Andrew Poulsom]

Have a look at the FileSearch object in VBA Help. The FileName property sets the name of the file to look for, and the TextOrProperty property sets the word or phrase to be searched for.

 

[parry]

Hi this should do the trick. Change the path and word/s to search for.

Sub FindText()
Dim i As Integer

'Search criteria
With Application.FileSearch
    .LookIn = "c:\my documents\logs" 'path to look in
    .FileType = msoFileTypeAllFiles
    .SearchSubFolders = False
    .TextOrProperty = "*Find*" '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

hth

 

[arkusM]

Hi this should do the trick. Change the path and word/s to search for.

Sub FindText()
Dim i As Integer
 
'Search criteria
With Application.FileSearch
    .LookIn = "c:\my documents\logs" 'path to look in
    .FileType = msoFileTypeAllFiles
    .SearchSubFolders = False
    .TextOrProperty = "*Find*" '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

hth

This works beautifully. Thank you.

 

[GRons]

Hi,
Now that FileSearch is not available in excel 2013, are we forced to go through all lines of text file 1 by 1 in order to test the presence of a string?
Isn't there a quicker way? Like when using the find function in excel?

In my case trying to go through 2370 text files (each weigthing ~20mb) takes ages.
I never had the patience to wait until the end of the sub before killing it!

Has anyone a piece of advise in order to make it quicker?

Option ExplicitSub FindRecords()
Dim Path, OutPath, FileName, tempPath, sTemp, CountName As String
Dim intFic, OutFile As Integer
Dim txtLine As String
Dim i, j, x, Count As Long
Dim retval




'Count # of lines
j = Range(Cells(5, 1), Cells(5, 1).End(xlDown)).Count


'Determine the general path to the folder
Path = Cells(1, 2).Value
'Dir the file to retrieve first file
CountName = Dir(Path)
'Count the number of files on the path
Do While CountName <> ""
    Count = Count + 1
    CountName = Dir()
Loop
Debug.Print Count
x = 0


'Dir the file to retrieve first file
FileName = Dir(Path)


'Loop through all the files and testing content
Do While FileName <> ""
    Debug.Print FileName
    tempPath = Path & FileName
    'Open the txt file currently being analyzed
    intFic = FreeFile
    Open tempPath For Input As intFic
        While Not EOF(intFic)
            Line Input #intFic, txtLine
                'Debug.Print txtLine
                For i = 5 To j + 4
                    If InStr(txtLine, Cells(i, 1).Value) Then
                        If InStr(txtLine, Format(Cells(i, 2), "yyyy-mm-dd")) Then
                            If InStr(txtLine, Cells(i, 3).Value) Then
                                sTemp = sTemp & txtLine & vbCrLf
                                Cells(i, 6) = FileName
                            End If
                        End If
                    End If
                Next i
        Wend
        'Debug.Print sTemp
        Close intFic
    FileName = Dir()
    x = x + 1
    Application.StatusBar = x & "/" & Count & "-" & FileName
Loop


'Generate the ouput file
OutPath = Path & "Results\"
OutFile = FreeFile
Open OutPath & "Query " & Format(Date, "yyyymmdd") & ".txt" For Output As OutFile
Print #OutFile, sTemp
Close OutFile
Application.StatusBar = False
End Sub

Thanks