Vlookup with External Sheet in VBA

[vikas_newports]

Hello
I hope all of you are doing well
I want to use vlookup from external file = Data and Worksheet = Support
as I already know how to use vlookup but I do not want to use workbook name and Worksheet name in my formula
this is the existing formula. Please help me to Write formula without using Workbook name and Worksheet Name in Formula

Range("B2").Formula = "=VLOOKUP(A2,'[DATA.xlsm]Support'!$A$2:$B$500,1,0)"

 

[vikas_newports]

any lead /?

 

[vikas_newports]

any lead /?

Any expert

 

[vikas_newports]

Due to no response, I have posted the same question to another help forum
Other Forum Link

 

[RoryA]

I don't understand the question. You can't look up from an external file without telling the formula which file and sheet it is; it just doesn't make sense.

 

[vikas_newports]

RoryA, Assume that if I used the Filename here in formula but someone changed the filename later on so it will not functional anymore
so the reason behind this question is that whatever the filename it should be working

 

[RoryA]

Excel is not psychic, so there is no solution for that. If someone saves the source under a different name while the linking workbook is open, the formula will automatically adjust, but not otherwise.

 

[vikas_newports]

Agree , can we get filename with filesize then use as a veriable in formulA

 

[RoryA]

I don't follow. How will you get the filename if you don't know what it is?

 

[vikas_newports]

I don't follow. How will you get the filename if you don't know what it is?

My knowledge is very limited in vba, if both files opened then we can do it ?