VBA复制SAP导出Excel文件数据执行数次后报找不到文件错误如何修复

核心问题根因 #

• SAP导出的Excel文件存在加载延迟，代码执行时文件还未完全被Excel进程识别，导致按名称查找工作簿时报错
• 反复调用Workbooks(文件名)的方式稳定性差，偶发检索失效
• 依赖剪贴板的Copy/Paste操作本身存在偶发失效概率，多次调用会提升报错风险
• 未完全消除的隐式Cells调用会导致上下文切换时指向错误工作表，触发范围读取异常

具体修复措施 #

1. 提前绑定所有工作簿、工作表对象 #

提前将需要操作的工作簿赋值给对象变量，避免每次调用时按名称检索，同时增加工作簿存在性校验和重试机制，适配文件加载延迟问题：

' 定义对象变量
Dim wbSource As Workbook, wbTarget As Workbook
Dim wsSource As Worksheet, wsTarget As Worksheet
Dim lastRowSource As Long, ostatni_transport As Long, TryCount As Integer

' 绑定目标母文件，最多重试5次，每次间隔1秒
On Error Resume Next
TryCount = 0
Do
    Err.Clear
    Set wbTarget = Workbooks(ten_plik)
    TryCount = TryCount + 1
    Application.Wait Now + TimeValue("00:00:01")
Loop Until Err.Number = 0 Or TryCount > 5
If TryCount > 5 Then MsgBox "找不到目标母文件": Exit Sub
On Error GoTo 0

' 绑定SAP导出的源文件，同样增加重试
On Error Resume Next
TryCount = 0
Do
    Err.Clear
    Set wbSource = Workbooks("Arkusz w ALVXXL01 (1)")
    TryCount = TryCount + 1
    Application.Wait Now + TimeValue("00:00:01")
Loop Until Err.Number = 0 Or TryCount > 5
If TryCount > 5 Then MsgBox "找不到SAP导出文件": Exit Sub
On Error GoTo 0

' 绑定工作表，后续所有操作直接使用变量，无需重复检索
Set wsSource = wbSource.Sheets(1)
Set wsTarget = wbTarget.Worksheets("TR")

2. 替换剪贴板复制为直接值赋值 #

完全弃用Copy/Paste操作，直接通过范围赋值传值，彻底解决剪贴板偶发失效问题，同时执行效率提升3倍以上：

' 提前读取源表、目标表最后一行，避免反复调用End(xlDown)
lastRowSource = wsSource.Cells(1, 1).End(xlDown).Row
ostatni_transport = wsTarget.Cells(1, 1).End(xlDown).Row

' A-G列赋值
wsTarget.Cells(ostatni_transport + 1, 1).Resize(lastRowSource - 1, 7).Value = _
    wsSource.Range(wsSource.Cells(2, 1), wsSource.Cells(lastRowSource, 7)).Value

' 目标J列对应源表H列
wsTarget.Cells(ostatni_transport + 1, 10).Resize(lastRowSource - 1, 1).Value = _
    wsSource.Range(wsSource.Cells(2, 8), wsSource.Cells(lastRowSource, 8)).Value

' 目标H列对应源表J列
wsTarget.Cells(ostatni_transport + 1, 8).Resize(lastRowSource - 1, 1).Value = _
    wsSource.Range(wsSource.Cells(2, 10), wsSource.Cells(lastRowSource, 10)).Value

' 目标I列对应源表K列
wsTarget.Cells(ostatni_transport + 1, 9).Resize(lastRowSource - 1, 1).Value = _
    wsSource.Range(wsSource.Cells(2, 11), wsSource.Cells(lastRowSource, 11)).Value

3. 可选适配：解决SAP导出文件名变动问题 #

如果多次导出后SAP生成的文件名后缀数字会递增（比如第二次导出为Arkusz w ALVXXL01 (2)），可以用前缀匹配的方式检索源文件，避免硬编码文件名导致的检索失败：

For Each wb In Application.Workbooks
    If wb.Name Like "Arkusz w ALVXXL01*" Then
        Set wbSource = wb
        Exit For
    End If
Next
If wbSource Is Nothing Then MsgBox "未找到SAP导出文件": Exit Sub

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