VLOOKUP From 2nd file in a macro

kcleere

Active Member
Joined
Oct 4, 2006
Messages
314
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
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

buxtongt

Well-known Member
Joined
Oct 5, 2011
Messages
709
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

Active Member
Joined
Oct 4, 2006
Messages
314
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

Well-known Member
Joined
Oct 5, 2011
Messages
709
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

Active Member
Joined
Oct 4, 2006
Messages
314

ADVERTISEMENT

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

Thanks
 

kcleere

Active Member
Joined
Oct 4, 2006
Messages
314
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

Well-known Member
Joined
Oct 5, 2011
Messages
709
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,484
Messages
5,596,407
Members
414,064
Latest member
Duncthegreat

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