'VBA does not include a method to retrieve a value from a closed file.
'however, you can take advantage of Excel's ability to work with linked files.
'outline code below retrieves values from a defined range in a closed workbook
'and returns them to a worksheet in the active workbook
'Application.WorksheetFunction.VLookup can then be used to lookup required
data
'its not elegant but should work!

Sub ClosedWBLookUp()

Dim rng As Range

lookupvalue = ThisWorkbook.Worksheets(1).Range("A1").Value

'location of workbook and worksheet & range
'where the data you want to lookup
'is located
mydata = "='C:\Desktop\[test3.xls]Sheet2'!$A$1:$C$18"

'range to return closed workbook data
Set rng = ThisWorkbook.Worksheets(2).Range("A1:C18")

'link to worksheet
rng.Formula = mydata

On Error Resume Next

lookupdata = Application.WorksheetFunction.VLookup(lookupvalue, rng, 3,
False)

If Err = 0 Then

MsgBox lookupdata

Else

MsgBox lookupvalue & " Not Found"

End If


On Error GoTo 0


'clear links
rng.Clear

End Sub
--
jb


"Dan" wrote:

I am trying (very unsuccessfully) to create a vba function that will be like
vlookup but will look in other sheets have 3 argument.
My problem is how to pass the reference to the other sheet to the function


I am aware I can do this without a function:
=VLOOKUP(A1,'C:\Desktop\[test3.xls]Sheet2'!$A$1:$C$18,3,FALSE)
But then if I try to do:
=VLOOKUP(A1,"'C:\Desktop\"&A2,3,FALSE)
A2= [test3.xls]Sheet2'!$A$1:$C$18
It does not work

Any idea?
Dan