Vlookup or another method to get info from closed excel document

Question

I have values from cells A1 to A20. I want to Vlookup each of them to get corresponding values from a closed Excel file. I found some ways that work, but I don't want to open any file even it stays hidden and don't want to write the Vlookup as a formula in any cell.

I tried Application.WorksheetFunction.VLookup and ExecuteExcel4Macro methods.

1 - This is a working example: ExecuteExcel4Macro to get value from closed workbook, but I wasn't able to alter this to work with Vlookup.

wbPath = "c:\users\fatihmi\Desktop\"
wbName = "Ornek.xlsx"
wsName = "Sheet1"

MsgBox ExecuteExcel4Macro("VLOOKUP(" & "testString" & ";" & "'" & wbPath & "[" & wbName & "]" & wsName & "'!$C:$E;3;FALSE)")

2 - I don't know how to reference or use a closed document with Application.WorksheetFunction.VLookup.

Dim wk As Workbooks
Set wk = "c:\users\fatihmi\Desktop\Ornek.xlsx"
Dim ws As Worksheet
Set ws = wk.Sheets("Sheet1")
Dim wr As Range
Set wr = ws.Range("C:E")
        
result = Application.WorksheetFunction.VLookup("testString", wr, 3, False)
MsgBox result

There is a possibility to get range with InputBox, but I don't know how to use raw range data as in code with Application.WorksheetFunction.VLookup.

Application.InputBox(prompt:="Enter range", Type:=8)

You just cannot do that. You need to open the sourcefile. You could open the sourcefile, input the data inside of an array and then perform the vlookup. But you will always need to open it even if its briefly for a second. — Damian
@Damian it's actually quite possible (if I understand the question) — JvdV

JvdV JvdV · Accepted Answer · 2019-09-02T11:48:42

You have made a few mistakes with the ExecuteExcel4Macro. It should work when:

You make sure to have double quotes around a string you search for.
Use the proper parameter delimiter (, isntead of ;)
If you make use of the r1c1 notation

Some information about the above can be found here

You can check the output of the following:

Debug.print "VLOOKUP(" & """testString""" & "," & "'" & wbPath & "[" & wbName & "]" & wsName & "'!r1c3:r20c5,3,FALSE)"

So the code should be:

wbPath = "c:\users\fatihmi\Desktop\"
wbName = "Ornek.xlsx"
wsName = "Sheet1"

MsgBox ExecuteExcel4Macro("VLOOKUP(" & """testString""" & "," & "'" & wbPath & "[" & wbName & "]" & wsName & "'!r1c3:r20c5,3,FALSE)")

Note, refering to whole columns range can be tricky in r1c1 notation, so maybe you can suffice in changing the row to like 1000?

Edit

Might you have 1000's of rows, I'm sure using INDEX and MATCH combo will be faster. In that case the code might look a little longer but should be faster:

wbPath = "c:\users\fatihmi\Desktop\"
wbName = "Ornek.xlsx"
wsName = "Sheet1"

MsgBox ExecuteExcel4Macro("INDEX('" & wbPath & "[" & wbName & "]" & wsName & "'!r1c5:r5000c5" & ",MATCH(" & """testString""" & ",'" & wbPath & "[" & wbName & "]" & wsName & "'!r1c3:r5000c3,0))")

Vlookup or another method to get info from closed excel document

1 Answers