Hi guys,
I have two columns, A and B. Column A has names. Column B has prices that are VLOOKUPed from a closed file (based on the names in Column A). The problem is that the file from which I am pulling in prices is generated daily.
The filename is file_YYYYMMDD.xls and the file is created anew daily, with its filename reflecting today's date. Does anyone have any suggestions on how I could access it? After much research, I've come to the conclusion that INDIRECT and INDIRECT.EXT are not suitable, because I am referencing an external, closed workbook from within a VLOOKUP. Harlan Grove's pull() UDF seems to be built for this, but when I try to write
Code:
=VLOOKUP(B11,pull(MacroSheet!C5),3,FALSE)
I get #VALUE! in all my cells.
By the way, the value of MacroSheet!C5 is
Code:
'S:\[file_20050610.xls]SPB51'!$B1:$D200
.
The code for pull() function 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
Thanks so much for any help, guys! If this could be done any other way, please let me know, I am so stuck with this.