通过For循环批量提取多工作簿指定单元格数据的问题

你遇到的问题是字符串拼接错误：你把变量Name直接写在双引号包裹的公式字符串里，VBA会把[Name]当成固定文本，而不是替换成实际的文件名，所以Excel找不到对应文件，只能弹出手动选择窗口。

修正后的VBA代码 #

Dim FSO As Object
Dim Folder As Object
Dim File As Object
Dim fileName As String
Dim i As Integer

Set FSO = CreateObject("Scripting.FileSystemObject")
Set Folder = FSO.GetFolder("C:\Users\Callum\Documents\Test Logs")

' 初始化行号，从第1行开始
i = 1

For Each File In Folder.Files
    ' 只处理Excel文件（.xlsx/.xls/.xlsm等，可根据需要补充）
    If LCase(FSO.GetExtensionName(File.Name)) Like "xls*" Then
        fileName = File.Name
        
        ' 直接写入公式，不用Select，提高效率
        ' 用&拼接字符串和变量，把实际文件名插入公式
        Sheets("Data").Cells(i, 1).FormulaR1C1 = "='[" & fileName & "]Sheet1'!R4C2"
        
        i = i + 1
    End If
Next File

' 释放对象
Set File = Nothing
Set Folder = Nothing
Set FSO = Nothing

关键改进点 #

• 字符串拼接：用&把公式的固定部分和fileName变量连接，"='[" & fileName & "]Sheet1'!R4C2"会生成带实际文件名的公式
• 避免Select：直接通过Sheets("Data").Cells(i, 1)定位单元格，不用切换工作表，提升代码稳定性和效率
• 过滤文件类型：只处理Excel格式的文件，避免把文件夹里的其他无关文件（比如.txt/.pdf）加入处理
• 初始化变量：明确设置i = 1，避免因变量默认值导致的行号混乱
• 释放对象：最后清空FSO相关对象，避免内存占用

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