Vlookup in VBA referencing to a closed workbook

Question

I want to create a function that takes a string as input, use this string as argument in a vlookup to get a value from a closed workbook.

The following code works when the data and the string are both in the same worksheet:

Function get_value(inputString as String)

    Dim dataRange as Range

    Set dataRange = Range("A1:B4")

    get_value = Application.WorksheetFunction.Vlookup(inputString, dataRange, 2, False)

End Function

Simply referencing the range (like the code below) doesn't work (I assumed that this is because Functions can't handle Workbooks.Open like Subs).

Set workbookVariable = Application.Workbooks.Open(path_to_file)

dataRange = workbookVariable.Sheets(1).Range("A1:B4")

My table (saved as 'names.xls', saved in Desktop) looks like:

           A       |        B
    1    Olivia    |       Spaghetti
    2    John      |       Steak
    3    Samuel    |       Rice
    4    Brian     |       Chicken

I want want a function call like:

=get_value(A1) and that will return the food of the name in A1.

How can I adjust my code so get_value works on other workbooks too ?

This may give you an idea, not tested thou. With the external workbook active, get the string from immediate window for Thisworkbook.Path & Application.PathSeparator & Range("A1:B4").Address(External:=true). You may need parts of it and setup Connection to that workbook. — PatricK

bmartin598 bmartin598 · Accepted Answer · 2017-07-18T14:42:17

Function auto_open()

Workbooks.Open ("C:\Users\bmartin598\desktop\name")

Workbooks("Book2").Activate 
'make this match the workbook this code is in

End Function



Function get_value(inputString As String)

Dim dataRange As Range

Dim workbookVariable As Workbook

Dim strVal As Variant

Set workbookVariable = Workbooks("name") 'change to match workbook name

Set dataRange = workbookVariable.Sheets(1).Range("A1:B4")

strVal = Workbooks("name").Sheets("Sheet1").Range(inputString).Value 
'make sure workbooks matches the name of your workbook

get_value = WorksheetFunction.VLookup(strVal, dataRange, 2, False)


End Function

Function auto_close()
Workbooks("name").Close
End Function

You can use this as a workaround. It automatically opens the other workbook whenever this one is open and puts it in the background. When you close the workbook you are working in the reference workbook closes as well.

Hope this helps.

Vlookup in VBA referencing to a closed workbook

1 Answers