Vba Excel do vlookup from a closed file

Question

I would like to do a vlookup from a close file to an active open file, The path of the closed file will be given as a variant

Lets say mypath = "C:list...."

I don't know if it is better to set the path only or the full-path (path+filename)

Could u please help ?

Answer 1

You will need the full path.

Example

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 function