VBA复制工作表内容报错：无法找到指定路径下的文件

问题根源分析 #

你的代码存在几个关键错误，导致文件找不到以及功能失效：

• 路径字符串错误添加双引号：构造filePath时用Chr(34)给路径套了双引号，Workbooks.Open接收的路径不需要额外加引号，这会让系统识别的路径包含无效引号，自然找不到文件。
• Range引用缺少引号：Range(B1)和Range(B2)中的单元格地址未用引号包裹，VBA会把B1、B2当成变量而非单元格地址，导致获取的路径或文件名列表错误。
• 复制后未执行粘贴操作：仅执行Copy但未粘贴，数据不会被复制到目标工作表。
• 其他潜在问题：i as Integer的as应为大写As；Dir仅获取第一个匹配文件，若有多个同名前缀文件会遗漏；直接用工作簿名称引用易出错，建议用变量存储打开的工作簿。

修正后的完整代码 #

Sub CopyFilesToReport()
    Dim reportList As String, reportType As String, partialfilePath As String, filename As String, filePath As String
    Dim reportArray() As String
    Dim i As Integer
    Dim sourceWB As Workbook ' 用变量存储打开的源工作簿，避免名称引用出错

    On Error GoTo ErrorHandler

    ' 修正Range引用，添加引号指定单元格地址
    reportList = Range("B1").Value ' B1值为Testfile1,Testfile2
    reportArray = Split(reportList, ",")

    partialfilePath = Range("B2").Value ' B2值为E:\test files\excel-sheets

    For i = LBound(reportArray) To UBound(reportArray)
        reportType = "*" & reportArray(i) & "*"
        filename = Dir(partialfilePath & "\" & reportType)
        
        ' 循环处理所有匹配的文件（避免遗漏多个同名前缀文件）
        Do While filename <> ""
            filePath = partialfilePath & "\" & filename ' 去掉多余的双引号，直接拼接有效路径
            Set sourceWB = Workbooks.Open(filePath)

            ' 复制实际有数据的区域到目标工作表，替代全列复制提升效率
            sourceWB.Worksheets(1).UsedRange.Copy _
                Destination:=ThisWorkbook.Worksheets(1).Range("A1")
                
            ' 也可以用更高效的赋值方式（无需剪贴板）
            ' ThisWorkbook.Worksheets(1).Range("A1").Resize(sourceWB.Worksheets(1).UsedRange.Rows.Count, _
            '     sourceWB.Worksheets(1).UsedRange.Columns.Count).Value = sourceWB.Worksheets(1).UsedRange.Value

            sourceWB.Close SaveChanges:=False ' 关闭源工作簿，不保存避免弹窗
            filename = Dir ' 获取下一个匹配文件
        Loop
    Next i

    Exit Sub ' 正常结束，跳过错误处理

ErrorHandler:
    MsgBox "错误：" & Err.Description, vbExclamation
End Sub

额外优化说明 #

• 用UsedRange替代A1:XFD1048576，仅复制实际有数据的区域，大幅提升运行效率。
• 使用ThisWorkbook指代当前运行代码的工作簿，比硬编码Workbooks("Reports")更可靠，避免因工作簿名称修改导致出错。
• 循环调用Dir处理所有匹配文件，不会遗漏目录下的多个目标文件。

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