VBA to vlookup into another workbook without opening said workbook

Hi Excel folk!

Hopefully someone can help with my request...!

I have a workbook that I reference all the time with vlookups. Ideally, I would like to create a vba function that will stop me having to open the workbook all the time.


For example, the data set has many columns of information. Column A has a list of Employee Numbers. Then columns B - M have various data about that employee (address, phone number, bank details, job title, salary etc etc).

I would like the function to work so that if I type the employee number in a different workbook, it will return data from the column of my choosing. Basically, I want to create a fancy vlookup (through function or other VBA code) that will recognise the Employee ID then return the lookup with whichever number of columns I choose.

Any help would be greatly appreciated!

Thanks in advance,

Heathy!

Hi, heathy.
VLOOKUP does not work without opening the target workbook as far as I know.
I think it's possible by using ADO to query the workbook via SQL, though.
It is necessary to know the workbook's path and the sheet's name of the database.

If book path/name and sheet name are known, VLookUp should work in a cell

something like
=VLookUp(A1,'C:\test\[test.xlsx]sheet1'!A:B,2,False)

If book path/name and sheet name are known, VLookUp should work in a cell

something like
=VLookUp(A1,'C:\test\[test.xlsx]sheet1'!A:B,2,False)

Yes, it's true! I didn't know that! Thank you, jindon.