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)

Answer 1

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))")