如何在Excel VBA中筛选指定命名规则的文件并添加到表格列

修改后的完整代码 #

Sub CommandButton1_Click()
    Dim V As String
    Dim BrowseFolder As String
    Dim startRow As Long
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "选择文件夹"
        .Show
        On Error Resume Next
        Err.Clear
        V = .SelectedItems(1)
        If Err.Number <> 0 Then
            MsgBox "未选择文件夹！"
            Exit Sub
        End If
    End With
    BrowseFolder = CStr(V)
    
    ' 设置表头格式
    With Range("A1:B1")
        .Font.Bold = True
        .Font.Size = 12
    End With
    Range("A1").Value = "文件名"
    Range("B1").Value = "文件大小"
    
    ' 记录初始数据行位置，用于后续判断是否找到目标文件
    startRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
    
    ' 遍历文件夹及子文件夹，筛选目标文件
    ListFilesInFolder BrowseFolder, True
    
    ' 检查是否找到符合条件的文件
    If Cells(Rows.Count, 1).End(xlUp).Row < startRow Then
        ' 无匹配文件，标记单元格
        Cells(startRow, 1).Interior.ColorIndex = 3 ' 红色填充
        Cells(startRow, 1).Value = "未找到符合条件的文件"
    End If
    
    Columns("A:B").AutoFit
End Sub

Private Sub ListFilesInFolder(ByVal SourceFolderName As String, ByVal IncludeSubfolders As Boolean)
    Dim FSO As Object
    Dim SourceFolder As Object
    Dim SubFolder As Object
    Dim FileItem As Object
    Dim r As Long
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    
    ' 获取当前A列最后一行的下一行
    r = Cells(Rows.Count, 1).End(xlUp).Row + 1
    
    ' 遍历当前文件夹下的所有文件，筛选符合命名规则的文件
    For Each FileItem In SourceFolder.Files
        ' 匹配条件：以EAN开头，以notforprint.pdf结尾
        If FileItem.Name Like "EAN*notforprint.pdf" Then
            Cells(r, 1).Value = FileItem.Name
            Cells(r, 2).Value = FileItem.Size
            r = r + 1
        End If
    Next FileItem
    
    ' 递归遍历子文件夹（如果开启）
    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            ListFilesInFolder SubFolder.Path, True
        Next SubFolder
    End If
    
    ' 释放对象
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
End Sub

关键修改说明 #

• 文件筛选逻辑：用Like "EAN*notforprint.pdf"匹配文件名，*作为通配符匹配任意长度的中间字符，精准筛选以EAN开头、以指定字符串结尾的PDF文件。
• 无文件处理：主过程中记录遍历前的起始行，遍历结束后对比A列最后一行位置，若未新增行则判定无匹配文件，将对应单元格填充红色并添加提示文字。
• 优化细节：移除原代码中弹出文件名的MsgBox避免干扰；改用Rows.Count替代固定的65536行，适配新版Excel的行范围；调整表头文字为中文更直观。

内容的提问来源于stack exchange，提问作者Dato