| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Using TODAY() to generate a filename from where values are VLOOKUPed
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) 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
Last edited by dolik : June 13th 05 at 07:48 PM |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vlookup only a set of values. | Excel Worksheet Functions | |||
| Vlookup based on two lookup values | Excel Worksheet Functions | |||
| vlookup and filename returning same result on each sheet. | Excel Discussion (Misc queries) | |||
| Generate random numbers between two values and with a given mean | Excel Worksheet Functions | |||
| sum values between today and 6 months prior | Excel Worksheet Functions | |||
Threaded Mode
