VLookup From Closed Workbook

[Ark68]

I am trying to apply a vlookup formula to a closed workbookbook using this syntax ...

grm_crew1 = application.WorksheetFunction.VLookup(b_label,'D:\WSOP 2020\SupportData\[Facilities.xlsx]Facilities'!range("H:M"),6,false)

grm_crew is defined as string, b_label is string. The vlookup is being applied to columns H:M in the sheet ("Facilities"), in the workbook("Facilities.xlsx"), at the path ("D:\WSOP 2020\SupportData\") specified.

I am getting an "Expected: expression" error with the first apostrophe identified.

Looking to the kind folk here to help me identify where I went wrong.

 

[EFANYoutube]

try the file name in ""?

grm_crew1 = application.WorksheetFunction.VLookup(b_label,"'D:\WSOP 2020\SupportData\[Facilities.xlsx]Facilities'!range("H:M")",6,false)

 

[Fluff]

Try

grm_crew1 = Application.WorksheetFunction.VLookup(b_label, Range("'D:\WSOP 2020\SupportData\[Facilities.xlsx]Facilities'!H:M"), 6, False)

 

[Ark68]

Hi EFANYoutube! Thanks for sharing your suggestion. That error is gone, but replace with a new one: "Expected: list separator or )"

grm_crew1 = application.WorksheetFunction.VLookup(b_label,"'D:\WSOP 2020\SupportData\[Facilities.xlsx]Facilities!'range("H:M")",6,false)

In the range (as noted above in red.

 

[Fluff]

Forgot to mention, the other workbook will need to be open.

 

[EFANYoutube]

Thanks Fluff
You picked up my typo again

Excel Fan

 

[Ark68]

Ohhh ... so I can't use the vlookup of a closed workbook in this way? I was avoiding having to open up the workbook to find a single value.
Thanks all! I'll have to try another approach.

 

[Fluff]

You could put the formula in a cell & then assign the result to a variable.

 

[Ark68]

Yeah ... that was where I think I was going to go Fluff. Thanks for confirming that approach for me. Saves me from heading toward a dead end like this idea turned out to be.

 

[Fluff]

Glad we could help & thanks for the feedback.