VLOOKUP From 2nd file in a macro

[kcleere]

I have a macro that uses VLOOKUP to bring data from a separate file to the working file. For this work the separate file has to be open. For what I do with it this works fine as I can control the process.

If I give this to a user they will have to remember to have both files open for the routie to work.

Is there a better way to do this as I suspect the way I'm doing it it is crude.

Have patience with be because I "record" macros and I'm not that good at writing the VBA code.

Thanks

 

[buxtongt]

You should be able to use something along the lines of:

'D:\[Plants.XLS]Plants'!C1:C3

as the second argument in the vlookup. The stuff in the [] is the file name, after that, but before the ! is the tab name. Hope this helps.

 

[kcleere]

I think that's effectively what I have now which is:

"'=vlookup(L2,'[Site data.xls]Site Data'!$C$2:$D$70,2,false"

 

[buxtongt]

Have you tried adding the path info in there as well? In my case the file is located on the d drive, hence the D:\. Hope this helps.

 

[kcleere]

You're right I'm obviously missing that. I'll try it.

Thanks

 

[kcleere]

Thank you...it works perfectly now. I can make sure the user has the file on their hard disk in a specific location and this should to the trick.

 

[buxtongt]

Yes, that's the one possible hurdle with this, but as long as that file is in the set location you will be fine. Glad we were able to get things working for you.