EP05. “Files in a Directory 遍历目录下的文件”

首页 › Microsoft 工具 › Excel · VBA · Workbook and Worksheet Object · EP05

约 7 分钟· #EP05#Excel#Workbook and Worksheet Object

🔒 登录后可标记已读

这篇教你用 Dir 函数遍历某个资料夹里所有的 Excel 文件
逐一打开每个文件，读取里面的工作表名称，再关闭
最后把结果整理列在当前工作表上
前置知识是 Do While 循环和 Workbooks.Open/Close
学完能处理"批次扫描一堆 Excel 文件"这类需求

重点内容

适用版本

桌面版通用（Excel 365 / 2021 / 2019 等）。

事前准备

下载 Book1.xlsx 到 Book5.xlsx 五个范例文件，放进 C:\test\ 资料夹。

完整代码

Sub Files_In_A_Directory()
    ' 第1步：声明变量
    Dim directory As String, fileName As String, sheet As Worksheet, i As Integer, j As Integer

    ' 第2步：关闭屏幕更新，加快执行速度
    Application.ScreenUpdating = False

    ' 第3步：初始化目录变量，用 Dir 函数找第一个符合条件的文件
    directory = "c:\test\"
    fileName = Dir(directory & "\*.xl??")

    ' 第4步：建立 Do While 循环，只要还找得到文件就继续
    Do While fileName <> ""

        ' 第5步：记录第几个文件，把文件名写进 A 列
        i = i + 1
        j = 2
        Cells(i, 1) = fileName

        ' 第6步：打开这个 Excel 文件
        Workbooks.Open (directory & fileName)

        ' 第7步：把这个文件里每张工作表的名称写进对应行
        For Each sheet In Workbooks(fileName).Worksheets
            Workbooks("files-in-a-directory.xlsm").Worksheets(1).Cells(i, j).Value = sheet.Name
            j = j + 1
        Next sheet

        ' 第8步：关闭这个文件
        Workbooks(fileName).Close

        ' 第9步：找下一个符合条件的文件
        fileName = Dir()

    Loop

    ' 第10步：恢复屏幕更新
    Application.ScreenUpdating = True
End Sub

代码逻辑说明

Dir(directory & "\*.xl??")：Dir 函数配合通配符 * 和 ?，可以搜出 .xls / .xlsx / .xlsm 等各种 Excel 文件
Do While fileName <> ""：Dir 函数每次调用会返回下一个符合条件的文件名，如果没有更多文件了就返回空字符串，循环靠这个条件自动结束
fileName = Dir()：在循环最后不带参数调用 Dir()，代表"继续找下一个"，不是重新开始搜索
从关闭状态的 Excel 文件读取工作表名称，必须先用 Workbooks.Open 把它打开才能读取，读完记得 Close 释放掉，避免开一堆文件占内存

代码怎么运行

Alt + F11 打开 VBA 编辑器，Insert → Module，贴入代码
把当前工作簿另存为 files-in-a-directory.xlsm
回到工作表放命令按钮，绑定 Files_In_A_Directory
点击按钮，A 列会列出资料夹里每个文件名，后面的列会列出该文件包含的工作表名称

[截图：宏运行完成后的工作表，A 列是各个文件名，右边几列是每个文件底下的工作表名称]

学完你会

✅ 能用 Dir 函数配合通配符，找出资料夹里所有 Excel 文件
✅ 能用 Do While 循环逐一打开、读取、关闭多个文件
✅ 知道 Application.ScreenUpdating 记得改回 True，避免画面卡住不刷新

常见错误

代码里 Workbooks("files-in-a-directory.xlsm") 这段写死了文件名，如果把工作簿另存成别的名字，代码要跟着改，否则会报"找不到工作簿"的错误
忘记在最后把 Application.ScreenUpdating 改回 True，会导致后续操作画面一直不刷新
Dir 函数一次只能维持一个搜索状态，如果在循环中间又调用了另一个 Dir(...) 搜索别的条件，会打断原本这个循环的搜索进度

Sources

Blog / Website：

Files in a Directory