Dynamic vlookup from a close workbook

[Dan]

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

 

[Barb Reinhardt]

I'd try changing

=VLOOKUP(A1,"'C:\Desktop\"&A2,3,FALSE)

to

=VLOOKUP(A1,indirect("'C:\Desktop\"&A2),3,FALSE)

HTH,
Barb Reinhardt

 

[Dave Peterson]

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.

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

 

[Barb Reinhardt]

Thanks. I'd forgotten about that. I've gone away from it becuase all the
workbooks I'm trying to access are on the West coast and I'm on the east
coast and it takes FOREVER to update the calculations. I'm guessing that the
function opens each workbook and gets the data. I found that if I
recalculated, it sometimes took hours to update because I used this function
so many times.

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.

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

 

[john]

'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