VBA读取文件夹最新文件执行VLOOKUP时出现Run-time error 1004求助

我编写了一段VBA代码，用于打开指定文件夹中的最新Excel文件，并通过VLOOKUP函数从该文件的「Sheetname with input data」工作表A:I区域取值，将对应I列的数据返回至当前工作簿的I2单元格。但运行时出现Run-time error 1004错误，尽管已通过wbname = ActiveWorkbook.Name记录目标工作簿名称，且指定了正确的目标单元格I2，仍无法定位错误原因。错误出在以下公式赋值代码行：

Range("I2").Formula = _
      "=VLOOKUP(A2,[" & MyPath & LatestFile & "]'Sheetname with input data'!A:I,9,False)"

完整代码如下：

Sub PrepareforOutlookMails()
    wbname = ActiveWorkbook.Name
    Dim MyPath As String
    Dim MyFile As String
    Dim LatestFile As String
    Dim LatestDate As Date
    Dim LMD As Date
    Dim wb As Workbook
    Dim fileLocation As String
    Dim fileToOpen As Workbook
    
    
    MyPath = "C:\1.ER\1.Work\19.Etr\Recon\2022\October"
        If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
        
        'first Excel file from the folder
        MyFile = Dir(MyPath & "*.xls", vbNormal)
        
        'If no files exit the sub
        If Len(MyFile) = 0 Then
            MsgBox "No files were found...", vbExclamation
            Exit Sub
        End If
        
        'Loop through each Excel file in the folder
        Do While Len(MyFile) > 0
        
            LMD = FileDateTime(MyPath & MyFile)
            
            If LMD > LatestDate Then
                LatestFile = MyFile
                LatestDate = LMD
            End If
            
    
            MyFile = Dir
            
        Loop
    Workbooks.Open MyPath & LatestFile
    Workbooks(wbname).Activate
    
    Range("I2").Formula = _
      "=VLOOKUP(A2,[" & MyPath & LatestFile & "]'Sheetname with input data'!A:I,9,False)"
End Sub

错误原因 #

1. 文件路径引用错误：当目标工作簿已经通过Workbooks.Open打开后，VLOOKUP公式中不需要带完整路径，仅需工作簿名称即可。带完整路径会让Excel识别为引用未打开的文件，路径中的特殊字符或文件已打开的状态会触发1004错误。
2. 未明确指定工作表：Range("I2")未指定所属工作表，默认使用当前激活的工作表，若激活目标工作簿后当前工作表并非需要赋值的工作表，也会导致错误。

修复方案 #

方案1：简化公式中的工作簿引用 #

去掉公式中的MyPath，仅保留已打开的工作簿名称：

Range("I2").Formula = _
  "=VLOOKUP(A2,[" & LatestFile & "]'Sheetname with input data'!A:I,9,False)"

方案2：明确指定目标工作表 #

避免依赖激活状态，直接指定当前工作簿的目标工作表（示例为Sheet1，需根据实际修改）：

Workbooks(wbname).Worksheets("Sheet1").Range("I2").Formula = _
  "=VLOOKUP(A2,[" & LatestFile & "]'Sheetname with input data'!A:I,9,False)"

方案3：优化工作簿引用方式 #

用变量存储打开的工作簿，后续引用更清晰可靠：

' 修改打开文件的代码，赋值给变量
Set fileToOpen = Workbooks.Open(MyPath & LatestFile)

' 公式中使用变量获取工作簿名称
Workbooks(wbname).Worksheets("Sheet1").Range("I2").Formula = _
  "=VLOOKUP(A2,[" & fileToOpen.Name & "]'Sheetname with input data'!A:I,9,False)"

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