string - vba数组 - vba查找单元格内容




如何在使用VBA的许多文本.log文件之一中找到特定的字符串? (2)

这是我到目前为止找到文件夹中的所有日志文件的代码。 但是我需要能够在每个文件中找到一个特定的字符串,如果它在一个文件中找到,停止查找并退出循环并报告它所在的文件名。

似乎有很多不同的方式来打开一个文件,并搜索它,我不知道哪一个是最好的,我通常不使用VBA,但这是目前我所有的访问。

在附注中,将会有最多36个日志文件,每个文件最多5MB。

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

我已经找到了这个代码,但似乎在Excel + VBA Application.FileSearch的2007+版本被淘汰:

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

Application.FileSearch在2007+版本的Excel中被删除。 后来,我发现这个功能复制它。 我有时会使用它,但通常我只是使用FileSystemObjectDir

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

此代码:

  • 查找所有*.log文件扩展名C:\MyDownloads\

  • 打开每个*.log文件并读取每一行

  • 如果找到了字符串 MAGIC ,那么它将在Immediate WidnowCTRL + 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




text-files