Vlookup to a separate Workbook

[polarisking]

The macro works when

• the LU .xlsx workbook is open, and
• is in the same directory as the macro, and
• I DON'T qualify the lookup workbook with a path

The macro fails when

• the LU .xlsx workbook is open, and
• IS NOT in the same directory as the macro, and
• I DO qualify (I have to, right?) the lookup workbook with a path
• The failure is "Subscript out of Range"

Sub VLUtest()

Dim Range_Lookup        As Range
Dim LU_Value            As Variant
Dim found_value         As Variant

LU_Value = 3

'Set Range_Lookup = Workbooks("TestVBALU-LookupTable.xlsx").Worksheets("sheet1").Range(("a1:b4")   'works

Set Range_Lookup = Workbooks("C:\Users\PatandDonna\Desktop\TestVBALU-LookupTable.xlsx").Worksheets("sheet1").Range("a1:b4")

found_value = Application.VLookup(LU_Value, Range_Lookup, 2, False)

Debug.Print found_value

If IsError(found_value) Then
'n/a would have been returned
Else
'no error
End If

End Sub

 

[NARAYANK991]

Hi ,

I would like to qualify the situations as follows :

If the file is not open , nothing works.

If the file is open , giving the full path does not work , irrespective of whether the file is in the same folder or in a different folder.

If the file is open , not giving the full path works , irrespective of whether the file is in the same folder or in a different folder.

Narayan

 

[polarisking]

Hi ,

I would like to qualify the situations as follows :

If the file is not open , nothing works.
CORRECT

If the file is open , giving the full path does not work , irrespective of whether the file is in the same folder or in a different folder.
CORRECT

If the file is open , not giving the full path works , irrespective of whether the file is in the same folder or in a different folder.
CORRECT. Why is the heck does this even work? How does VBA even "find" the file?

Narayan

Narayan, I guess I'm "happy" that #3 is the case, buy can you please explain why this would be the case? I certainly understand the case where the location of the LU file could be intuited from the location of the .xlsm file. Does Excel make the location of each open Excel file available to VBA? If so, just when does qualifying the path become mandatory?

 

[NARAYANK991]

Hi ,

I think giving the full path of a file is relevant only when you open / save a file ; when used implicitly , as you are doing , I think it is not relevant.

When used implicitly , Excel expects the file to be already open ; of course this behaviour is different from an Excel worksheet formula , where if you have an external link , the full path name of the link is stored , so that the formula can return the right result even when the external linked file is not open. However , once the file is open , then Excel automatically changes the formula to refer to the open workbook without the full path !

Narayan

 

[polarisking]

Narayan, I cannot thank you enough. You've helped me out a couple of times recently, and awhile back "liked" a piece of code I submitted to optimize another problem. It's wordy, but ChandooNarayan.org might be on the horizon.