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 |
#2
|
|||
|
|||
As an outline of a possible solution ...
Record a query using MsQuery on your source file. You can amend that query and use replacable parameters. The data from the query will go into a range somewhere within your target workbook and you can lookup values from there. -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "dolik" wrote in message ... 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. -- dolik |
#3
|
|||
|
|||
Why not write Auto_Open code that redirects the link.
If it is the only link source for the workbook, you could use something like this: Sub Auto_Open() ThisWorkbook.ChangeLink ThisWorkbook.LinkSources(1, xlExcelLinks), _ ThisWorkbook.Path & "\MyFile_" & Format(Date, "yyyymmmdd") & ".xls" End Sub Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
Reply |
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 |