VBA编程-获取文件夹下面的文档名称

原创已于 2024-08-26 17:22:42 修改 · 1.9k 阅读

2 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#excel

于 2024-04-25 17:24:45 首次发布

本文介绍了一段VBA代码，该代码能够遍历指定文件夹及其子文件夹中的所有Office文档（如Excel）和PDF文件，并将这些文件的名称记录到Excel工作表Sheet1中。通过使用FileSystemObject对象，代码实现了文件类型过滤和递归处理。

Sub GetDocNames()
    Dim folderPath As String
    Dim fso As Object
    Dim folder As Object
    Dim subFolder As Object
    Dim file As Object
    Dim ws As Worksheet
    Dim row As Long
    
    ' 弹出窗口选择文件夹
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = -1 Then
            folderPath = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With
    
    ' 创建FileSystemObject对象
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    ' 获取文件夹对象
    Set folder = fso.GetFolder(folderPath)
    
    ' 设置工作表和起始行
    Set ws = ThisWorkbook.Sheets("Sheet1") ' 修改为你想要保存文档名称的工作表
    row = 1
    
    ' 遍历文件夹中的文件
    For Each file In folder.Files
        ' 如果是OFFICE文档或PDF文档
        If LCase(fso.GetExtensionName(file.Name)) Like "xls*" Or LCase(fso.GetExtensionName(file.Name)) = "pdf" Then
            ' 在工作表中保存文档名称
            ws.Cells(row, 1).Value = file.Name
            row = row + 1
        End If
    Next file
    
    ' 遍历子文件夹
    For Each subFolder In folder.Subfolders
        ' 递归调用自身处理子文件夹
        ProcessFolder subFolder, ws, row
    Next subFolder
    
    ' 释放对象
    Set subFolder = Nothing
    Set folder = Nothing
    Set fso = Nothing
End Sub

Sub ProcessFolder(folder As Object, ws As Worksheet, ByRef row As Long)
    Dim subFolder As Object
    Dim file As Object
    Dim fso As Object
    
    ' 创建FileSystemObject对象
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    ' 遍历文件夹中的文件
    For Each file In folder.Files
        ' 如果是OFFICE文档或PDF文档
        If LCase(fso.GetExtensionName(file.Name)) Like "xls*" Or LCase(fso.GetExtensionName(file.Name)) = "pdf" Then
            ' 在工作表中保存文档名称
            ws.Cells(row, 1).Value = file.Name
            row = row + 1
        End If
    Next file
    
    ' 遍历子文件夹
    For Each subFolder In folder.Subfolders
        ' 递归调用自身处理子文件夹
        ProcessFolder subFolder, ws, row
    Next subFolder
    
    ' 释放对象
    Set subFolder = Nothing
    Set file = Nothing
    Set fso = Nothing
End Sub

此代码可以识别文件夹里面的文件夹一直到最后