VBA Vlookup跨工作簿调用问题：SAP导出报表无法执行公式

问题根源 #

原代码的核心错误是：打开Bank.xlsx后，ActiveWorkbook自动切换为该数据源文件，导致wb1指向的是Bank.xlsx的Sheet1，而非SAP提取的报表工作簿，自然无法在目标报表上写入公式。

修正后的代码 #

Sub VlookupFromClosedFile()
    Dim wbTarget As Workbook ' 指向SAP提取的报表工作簿
    Dim wbSource As Workbook ' 指向数据源Bank.xlsx
    Dim sourcePath As String
    
    ' 打开数据源前，先捕获当前激活的SAP报表工作簿
    Set wbTarget = ActiveWorkbook
    
    ' 数据源文件的完整路径
    sourcePath = "C:\User\1.Work\18.SAP GUI and Automation\Payment propsal Test\Bank.xlsx"
    
    ' 打开数据源工作簿
    Set wbSource = Workbooks.Open(sourcePath)
    
    ' 在SAP报表的Sheet1单元格AA2写入VLOOKUP公式
    With wbTarget.Sheets("Sheet1")
        ' 用完整路径引用，关闭数据源后公式仍可正常工作
        .Range("AA2").Formula = "=VLOOKUP(D1,'" & sourcePath & "'!Sheet1!A:C,3,FALSE)"
        ' 若需保持数据源打开，可改用简化格式：
        ' .Range("AA2").Formula = "=VLOOKUP(D1,'[Bank.xlsx]Sheet1'!A:C,3,FALSE)"
    End With
    
    ' 可选：关闭数据源工作簿，不保存任何更改
    wbSource.Close SaveChanges:=False
End Sub

关键说明 #

• 提前捕获目标工作簿：必须在打开数据源文件前执行Set wbTarget = ActiveWorkbook，确保指向SAP提取的报表（此时该报表是激活状态）。
• 明确工作簿指向：用wbTarget替代依赖ActiveWorkbook的写法，避免因工作簿切换导致的指向错误。
• 外部引用格式：如果需要关闭数据源文件，公式必须使用完整文件路径的引用格式，这样即使数据源关闭，公式仍能从本地文件读取数据。

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