Microsoft VBA Excel 读取文件夹文件名

项目场景

简述：
需要一个VBA程序，能从指定文件夹读取全部文件名称，然后逐个输入到指定的"文件名称"列内（第一个单元格定义为FileName）。

---

代码描述

Sub ListFilesInFolderStartingFromFileNameCell()
    Dim FileSystem As Object
    Dim Folder As Object
    Dim File As Object
    Dim ws As Worksheet
    Dim FolderPath As String
    Dim FileNameCell As Range
    Dim i As Integer

    ' 设置文件夹路径
    FolderPath = "C:\Your\Folder\Path" ' 修改文件夹路径

    ' 确保文件夹路径以反斜杠结束
    If Right(FolderPath, 1) <> "\" Then FolderPath = FolderPath & "\"

    ' 创建FileSystemObject
    Set FileSystem = CreateObject("Scripting.FileSystemObject")
    Set Folder = FileSystem.GetFolder(FolderPath)
    Set ws = ThisWorkbook.Sheets("File_Summary")

    ' 找到名称为"FileName"的单元格
    Set FileNameCell = ws.Range("FileName")

    ' 从"FileName"单元格下一行开始
    i = FileNameCell.Row + 1

    ' 遍历文件夹中的每个文件
    For Each File In Folder.Files
        ' 文件名称写入Excel中
        ws.Cells(i, FileNameCell.Column).Value = File.Name
        i = i + 1
    Next File

    ' 清理
    Set File = Nothing
    Set Folder = Nothing
    Set FileSystem = Nothing
End Sub