Vba Excel从关闭的文件执行vlookup

您将需要完整路径。

示例

Option Explicit
Public Sub Example()
    Dim Path As String
    Path = "C:\Temp\"
    With ThisWorkbook.Sheets("Sheet1")
        .Range("B1").Formula = "=VLOOKUP(A1,'" & Path & "[Book1.xlsx]Sheet2'!A:J,3,FALSE)"
    End With
End Sub

VLOOKUP函数

只要@ 0m3r的解决方案回答了这个问题，对于希望引用位于 Desktop.中的已关闭文件的任何人来说，这都是一个很小的贡献

Even not specific for one user, works on any other user as well.

Option Explicit
Public Sub Example()
    Dim yourdesktopaddress As String 
    yourdesktopaddress = CreateObject("WScript.Shell").specialfolders("Desktop")
'or if you would like to reference another folder located in Desktop (i.e: SpecificFolderOnTheDesktop)
    'Dim folderinthedesktopaddress as String
    'folderinthedesktopaddress = CreateObject("WScript.Shell").specialfolders("Desktop") & "\SpecificFolderOnTheDesktop"
    With ThisWorkbook.Sheets("Sheet1")
        .Range("B1").Formula = "=VLOOKUP(A1,'" & yourdesktopaddress & "[Book1.xlsx]Sheet2'!A:J,3,FALSE)"
    End With
End Sub