Trying To Access A Closed Workbook Results In Open File Window

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,868
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.

Code:
        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.
 
Last edited:

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,868
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
This code BTW is in my userform initialization procedure in case that makes a difference?
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,868
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You need to concatenate it in. If it's a string, it needs to be in quotes, so:

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

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

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

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,868
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I ran out to get a coffee and then it dawned on me!! But thanks Rory for reaffirming my thought!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,493
Messages
5,636,641
Members
416,932
Latest member
mm07

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top