Trying To Access A Closed Workbook Results In Open File Window

[Ark68]

EDIT: I solved the initial problem ... but see the new issue unrelated to the title (which I can't change)

I am trying to get a vlookup value from a closed workbook by applying the formula to a cell in a worksheet to refer to.

        With ws_thold
            .Cells(1, 26) = "=VLOOKUP(pnum,('D:\WSOP 2020\[permit_data.xlsx]Permit_Data'!$A3:$E2000),5,FALSE)"
        End With

The result is #NAME.

The path exists ("D:\WSOP 2020"), the file name is correct ("permit_data.xlsx"), the worksheet name is correct (Permit_Data), column A holds permit numbers (pnum) and column E has the returning value.
In my testing, the permit number does exist in column A.

 

[Ark68]

This code BTW is in my userform initialization procedure in case that makes a difference?

 

[Ark68]

I think the problem is with the variable "pnum". Although the string variable of "pnum" has a value going into the code, I don't think its being applied.
Can I not use a variable in this situation? Do I need to write the value to a cell, and then reference that cell as my lookup value in the formula?

 

[RoryA]

You need to concatenate it in. If it's a string, it needs to be in quotes, so:

"=VLOOKUP(""" & pnum & """,('D:\WSOP 2020\[permit_data.xlsx]Permit_Data'!$A3:$E2000),5,FALSE)"

or if it's a number, omit the quotes:

"=VLOOKUP(" & pnum & ",('D:\WSOP 2020\[permit_data.xlsx]Permit_Data'!$A3:$E2000),5,FALSE)"

 

[Ark68]

I ran out to get a coffee and then it dawned on me!! But thanks Rory for reaffirming my thought!