使用Excel VBA列出某些模式的文件 [英] List files of certain pattern using Excel VBA

问题描述

如何在用户指定的目录中列出与特定模式匹配的所有文件？这应该在所选目录的子文件夹中递归地工作。我还需要一个方便的方式（如树控件）列出它们。

How to list all the files which match a certain pattern inside a user specified directory? This should work recursively inside the sub folders of the selected directory. I also need a convenient way(like tree control) of listing them.

推荐答案

看来，一对夫妇回答谈论递归，一个关于正则表达式。以下是将两个主题放在一起的一些代码。我从 http://vba-tutorial.com 获取代码

It appears that a couple answers talk about recursion, and one about regex. Here's some code that puts the two topics together. I grabbed the code from http://vba-tutorial.com

Sub FindPatternMatchedFiles()

    Dim objFSO As Object
    Set objFSO = CreateObject("Scripting.FileSystemObject")

    Dim objRegExp As Object
    Set objRegExp = CreateObject("VBScript.RegExp")
    objRegExp.pattern = ".*xlsx"
    objRegExp.IgnoreCase = True

    Dim colFiles As Collection
    Set colFiles = New Collection

    RecursiveFileSearch "C:\Path\To\Your\Directory", objRegExp, colFiles, objFSO

    For Each f In colFiles
        Debug.Print (f)
        'Insert code here to do something with the matched files
    Next

    'Garbage Collection
    Set objFSO = Nothing
    Set objRegExp = Nothing

End Sub

Sub RecursiveFileSearch(ByVal targetFolder As String, ByRef objRegExp As Object, _
                ByRef matchedFiles As Collection, ByRef objFSO As Object)

    Dim objFolder As Object
    Dim objFile As Object
    Dim objSubFolders As Object

    'Get the folder object associated with the target directory
    Set objFolder = objFSO.GetFolder(targetFolder)

    'Loop through the files current folder
    For Each objFile In objFolder.files
        If objRegExp.test(objFile) Then
            matchedFiles.Add (objFile)
        End If
    Next

    'Loop through the each of the sub folders recursively
    Set objSubFolders = objFolder.Subfolders
    For Each objSubfolder In objSubFolders
        RecursiveFileSearch objSubfolder, objRegExp, matchedFiles, objFSO
    Next

    'Garbage Collection
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objSubFolders = Nothing

End Sub

这篇关于使用Excel VBA列出某些模式的文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！