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 
 =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 are: 
 'C:\folder\[file_20050602.xls]Tab1'!$B1:$D200
The UDF code for PULL() is: 

 '----- 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!