Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic vlookup from a close workbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic vlookup from a close workbook
I'd try changing
=VLOOKUP(A1,"'C:\Desktop\"&A2,3,FALSE) to =VLOOKUP(A1,indirect("'C:\Desktop\"&A2),3,FALSE) HTH, Barb Reinhardt "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic vlookup from a close workbook
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. 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic vlookup from a close workbook
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. "Dave Peterson" wrote: 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. 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic vlookup from a close workbook
'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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic chart pasted to a new workbook in report can't be dynamic | Charts and Charting in Excel | |||
Hide all Sheets in Workbook then Close Workbook | Excel Programming | |||
Open New Workbook / Save and Close Current Workbook | Excel Programming | |||
Closing a workbook from a macro doesn't close the workbook | Excel Programming | |||
Help on Workbook close and workbook save events | Excel Programming |