Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 1
Default 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)
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.

Last edited by dolik : June 13th 05 at 07:48 PM
  #2   Report Post  
Andy Wiggins
 
Posts: n/a
Default

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   Report Post  
Bill Manville
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
vlookup only a set of values. sansk_23 Excel Worksheet Functions 7 May 18th 05 01:31 PM
Vlookup based on two lookup values Trip Excel Worksheet Functions 2 April 8th 05 06:25 PM
vlookup and filename returning same result on each sheet. RogueSwan Excel Discussion (Misc queries) 3 March 22nd 05 11:08 PM
Generate random numbers between two values and with a given mean pinosan Excel Worksheet Functions 2 March 7th 05 03:04 PM
sum values between today and 6 months prior Qaspec Excel Worksheet Functions 3 January 19th 05 09:17 PM


All times are GMT +1. The time now is 05:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"