Dynamic VLOOKUP from external, closed workbooks
 

Hi guys,

I have a sheet which is feeding in market prices from another workbook.
It has bond names in col A and vlookup of prices based on those names in
col B. The prices are pulled in from a file which is generated daily
(since prices are updated daily). So, today, the external file would be
called file_20050606.xls. I need vlookup to find today's file every day
and pull the prices from there. I've tried creating a volatile filename
path using TODAY() and then using INDIRECT to point to the cell with
that file path but...it doesn't work with closed worksheets. And
INDIRECT.EXT won't help either because it doesn't work with ranges,
making it useless within VLOOKUPs

I've searched around and found that Harlan Grove wrote a pull()
function which does what I'm trying to do. I've pasted it into a new
module in my VBA, but when I write
Code:
--------------------
=VLOOKUP(B7,pull(MacroSheet!C5),3,False)
--------------------
All I get are #VALUE in all the vlooked-up cells

Am I using incorrect syntax?

By the way, the contents of MacroSheet!C5 a

Code:
--------------------
'C:\folder\[file_20050602.xls]Tab1'!$B1:$D200
--------------------


The UDF code for PULL() is:


Code:
--------------------

'----- begin VBA -----
Function pull(xref As String) As Variant

Dim xlapp As Object, xlwb As Workbook
Dim b As String, r As Range, C As Range, n As Long

'** begin 2004-05-28 changes **
'** begin 2004-03-25 changes **
n = InStrRev(Len(xref), xref, "\")

If n 0 Then
If Mid(xref, n, 2) = "\[" Then
b = Left(xref, n)
n = InStr(n + 2, xref, "]") - n - 2
If n 0 Then b = b & Mid(xref, Len(b) + 2, n)

Else
n = InStrRev(Len(xref), xref, "!")
If n 0 Then b = Left(xref, n - 1)

End If

'** key 2004-05-28 addition **
If Left(b, 1) = "'" Then b = Mid(b, 2)

On Error Resume Next
If n 0 Then If Dir(b) = "" Then n = 0
Err.Clear
On Error Goto 0

End If

If n <= 0 Then
pull = CVErr(xlErrRef)
Exit Function
End If
'** end 2004-03-25 changes **
'** end 2004-05-28 changes **

pull = Evaluate(xref)

If CStr(pull) = CStr(CVErr(xlErrRef)) Then
On Error Goto CleanUp 'immediate clean-up at this point

Set xlapp = CreateObject("Excel.Application")
Set xlwb = xlapp.Workbooks.Add 'needed by .ExecuteExcel4Macro

On Error Resume Next 'now clean-up can wait

n = InStr(InStr(1, xref, "]") + 1, xref, "!")
b = Mid(xref, 1, n)

Set r = xlwb.Sheets(1).Range(Mid(xref, n + 1))

If r Is Nothing Then
pull = xlapp.ExecuteExcel4Macro(xref)

Else
For Each C In r
C.Value = xlapp.ExecuteExcel4Macro(b & C.Address(1, 1, xlR1C1))
Next C

pull = r.Value

End If

CleanUp:
If Not xlwb Is Nothing Then xlwb.Close 0
If Not xlapp Is Nothing Then xlapp.Quit
Set xlapp = Nothing

End If

End Function

--------------------


I was also wondering if I could use this function with cell names? Sort
of like =vlookup(B2, pull(CellName),3,FALSE) instead of specifying the
cell's location.

Thanks so much for any help!


--
dolik
------------------------------------------------------------------------
dolik's Profile: http://www.excelforum.com/member.php...o&userid=24244
View this thread: http://www.excelforum.com/showthread...hreadid=378513