VLOOKUP in a separate workbook that updates the file name daily

[tingatinga]

I am trying to use vlookup for a different workbook, but the file name of the workbook changes daily (it's updated everyday). The format of the file's name is consistent, and only changes according to the date, so today's file name would be "file_02132014.xls"

So far what I have that works is the following:

=VLOOKUP(B5,'\\URL\URL\URL\[file_02132014.xls]Sheet1'!$A$1:$U$956, 3, FALSE)​

But I have 100+ rows of this, and want to be able to make minimal inputs to get the correct, updated file. Basically, the I need the bolded number in the formula above to either update to the correct date using a function or link to a cell I can manually update.

Let me know if what I said needs clarification. Thanks in advance!

 

[Col Delane]

Welcome to the Forum

This solution will change the link to the new file by emulating what you would do manually by clicking Data \ Edit Links \ Change Source.

1. Identify two cells that are out of the way of your existing data/formulae
2. Assign the Defined Names "CurrentLink" to one, and "NewLink" to the other
3. Enter (as text) the full path and file name of the current file (i.e. ''\\URL\URL\URL\file_02132014.xls" - Note: do NOT include " or [ ]) into the first cell. (CurrentLink)
4. Enter (as text) the full path and file name of the new file into the second (NewLink)
5. Copy the following code to a standard module

Sub ChangeLink()

Dim r1 As Range
Dim r2 As Range
Dim OldLink As String
Dim NewLink As String

Set r1 = Range("CurrentLink")
OldLink = r1.Value

Set r2 = Range("NewLink")
NewLink = r2.Value

MsgBox "oldLink = " & OldLink
MsgBox "newLink = " & NewLink

   
'Change the link
ThisWorkbook.ChangeLink Name:=OldLink, NewName:=NewLink, Type:=xlExcelLinks
    
'Update input cells
r1.Value = r2.Value
r2.ClearContents

MsgBox "Done"

End Sub

 

[Col Delane]

Please ignore the previous post - I inadvertently clicked the Submit Reply button before I had finished compiling the post, and then ran out of time to edit the one I submitted! (The Submit Reply and Preview Post buttons are too close together!!)

This is the complete post:
Welcome to the Forum

This solution will change the link to the new file by emulating what you would do manually by clicking Data \ Edit Links \ Change Source.
But first, you need to do some set-up:
1. Identify two cells that are out of the way of your existing data/formulae
2. Assign the Defined Names "CurrentLink" to one, and "NewLink" to the other.
3. Copy the following code to a standard module for the workbook in the Visual Basic Editor

Sub ChangeLink()

Dim r1 As Range
Dim r2 As Range
Dim OldLink As String
Dim NewLink As String

Set r1 = Range("CurrentLink")
OldLink = r1.Value

Set r2 = Range("NewLink")
NewLink = r2.Value

MsgBox "oldLink = " & OldLink    ' Can be deleted/commented out if not required
MsgBox "newLink = " & NewLink   ' Can be deleted/commented out if not required


   
'Change the link
ThisWorkbook.ChangeLink Name:=OldLink, NewName:=NewLink, Type:=xlExcelLinks
    
'Update input cells ready for next change
r1.Value = r2.Value
r2.ClearContents

MsgBox "Link changed."

End Sub

When you want to change the link:
1. Enter (as text) the full path and file name of the current file into the CurrenLink cell (e.g. '\\URL\URL\URL\file_02132014.xls) Note: do NOT include quotes " or [ ].
2. Enter (as text) the full path and file name of the new file into the NewLink cell (same rules as for 1)
3. Run the macro, which will:

1. change the link from the old file to the new (and the reference to the file in your formulae will change accordingly) based on the values in the two named cells
2. replace the entry in the CurrentLink cell with the text value from NewLink ready for the next change
3. clear NewLink.
​

Hope this is a workable solution for you.