Live look ups to a file name that changes for version control

T

taubeam

New Member
Joined
Mar 6, 2014
Messages
49
Before I go bananas trying to figure out how to do this, can this be done?

I have to maintain a live link to a file that my co-worker names with a version number frequently.

Is there anyway to link for a look up to this file with maybe a wild card to account for the versioning? or to default to the last modified?

For example the name "02.Field Quota Planning 2018v##"
Is there anyway to update the look up to account for the changing version numbers and use the most recently saved version?
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
It probably can be done, but you need to be more specific. What do you mean by a look up and a live link? Is it a VLOOKUP or a HLOOKUP formula which references an external workbook? If so, you could have a VBA procedure which recreates the formula(s) when the main workbook is opened and calls the Dir function to loop through the external workbooks in a folder and calls the FileDateTime function to determine the last modified workbook.

The code at https://www.mrexcel.com/forum/excel...closed-workbooks-post4723265.html#post4723265 does something similar and could be adapted to your situation.
 
Upvote 0
John_w said:
It probably can be done, but you need to be more specific. What do you mean by a look up and a live link? Is it a VLOOKUP or a HLOOKUP formula which references an external workbook? If so, you could have a VBA procedure which recreates the formula(s) when the main workbook is opened and calls the Dir function to loop through the external workbooks in a folder and calls the FileDateTime function to determine the last modified workbook.

The code at https://www.mrexcel.com/forum/excel...closed-workbooks-post4723265.html#post4723265 does something similar and could be adapted to your situation.
Click to expand...

Thanks, for looking.

The formula is below
=INDEX('[02. Field Quota Planning 2018 v34.xlsx]All Reps'!$M:$M,MATCH(K4,'[02. Field Quota Planning 2018 v34.xlsx]All Reps'!$A:$A,0))

Where V34 is changed nearly every day
 
Upvote 0
Try this code in a standard module in your main workbook. Run the Create_Formula macro to create the =INDEX formula - which references the latest matching file in the same folder as the main workbook - in cell A2 of Sheet1. You could call this from the Workbook_Open event handler.

Code: 
Public Sub Create_Formula()

    Dim latestFileName As String
    
    latestFileName = FindLatestFile(ThisWorkbook.Path & "\02. Field Quota Planning 2018 v*.xlsx")
    
    If latestFileName <> "" Then    
        Workbooks.Open ThisWorkbook.Path & "\" & latestFileName
        ThisWorkbook.Worksheets("Sheet1").Range("A2").Formula = "=INDEX('[" & latestFileName & "]All Reps'!$M:$M,MATCH(K4,'[" & latestFileName & "]All Reps'!$A:$A,0))"  
    Else
        MsgBox "No files found matching '02. Field Quota Planning 2018 v*.xlsx' in " & ThisWorkbook.Path
    End If
    
End Sub


Private Function FindLatestFile(fullFileSpec As String) As String

    Dim folder As String, fileName As String
    Dim latestFileDate As Date
    
    FindLatestFile = ""
    
    folder = Left(fullFileSpec, InStrRev(fullFileSpec, "\"))
    latestFileDate = 0
    fileName = Dir(fullFileSpec, vbNormal)
    Do While fileName <> vbNullString
        If FileDateTime(folder & fileName) > latestFileDate Then
            FindLatestFile = fileName
            latestFileDate = FileDateTime(folder & fileName)
        End If
        fileName = Dir
    Loop
    
End Function
 
Upvote 0
John_w said:
Try this code in a standard module in your main workbook. Run the Create_Formula macro to create the =INDEX formula - which references the latest matching file in the same folder as the main workbook - in cell A2 of Sheet1. You could call this from the Workbook_Open event handler.

Code: 
Public Sub Create_Formula()

    Dim latestFileName As String
    
    latestFileName = FindLatestFile(ThisWorkbook.Path & "\02. Field Quota Planning 2018 v*.xlsx")
    
    If latestFileName <> "" Then    
        Workbooks.Open ThisWorkbook.Path & "\" & latestFileName
        ThisWorkbook.Worksheets("Sheet1").Range("A2").Formula = "=INDEX('[" & latestFileName & "]All Reps'!$M:$M,MATCH(K4,'[" & latestFileName & "]All Reps'!$A:$A,0))"  
    Else
        MsgBox "No files found matching '02. Field Quota Planning 2018 v*.xlsx' in " & ThisWorkbook.Path
    End If
    
End Sub


Private Function FindLatestFile(fullFileSpec As String) As String

    Dim folder As String, fileName As String
    Dim latestFileDate As Date
    
    FindLatestFile = ""
    
    folder = Left(fullFileSpec, InStrRev(fullFileSpec, "\"))
    latestFileDate = 0
    fileName = Dir(fullFileSpec, vbNormal)
    Do While fileName <> vbNullString
        If FileDateTime(folder & fileName) > latestFileDate Then
            FindLatestFile = fileName
            latestFileDate = FileDateTime(folder & fileName)
        End If
        fileName = Dir
    Loop
    
End Function
Click to expand...

Thank you so much, this worked great.

We're trying to add an auto fill now. I will have a great deal of formulas in different columns referencing this file. That's why I wanted to not have to remember all the places its linked.

Our auto fill tries have not worked. If you have time can you help with ours below?


Public Sub Create_Formula()


Dim latestFileName As String

latestFileName = FindLatestFile("\\Slnas3\nas\lib-SL\Sales\sales_enablement\001 New Stuff\Comp Plans\2018\Quota Planning" & "\02. Field Quota Planning 2018 v*.xlsx")
lastRow = Range("S4").End(xlDown).Row


If latestFileName <> "" Then
Workbooks.Open "\\Slnas3\nas\lib-SL\Sales\sales_enablement\001 New Stuff\Comp Plans\2018\Quota Planning" & "" & latestFileName
ThisWorkbook.Worksheets("Detail").Range("T4").Formula = "=INDEX('[" & latestFileName & "]All Reps'!$M:$M,MATCH(K4,'[" & latestFileName & "]All Reps'!$A:$A,0))"
Else
MsgBox "No files found matching '02. Field Quota Planning 2018 v*.xlsx' in " & ThisWorkbook.Path
End If
Range("T4").AutoFill Destination:=Range(Range("T4"), Range("T" & lastRow))



End Sub
 
Upvote 0
The Workbooks.Open makes the opened file the active workbook and your AutoFill statement uses an unqualified Range reference so it uses the active sheet in the active workbook. Try this:

Code: 
Public Sub Create_Formulas()

    Dim lastRow As Long
    Dim planningFolder As String
    Dim latestFileName As String
    
    planningFolder = "\\Slnas3\nas\lib-SL\Sales\sales_enablement\001 New Stuff\Comp Plans\2018\Quota Planning"
    lastRow = ThisWorkbook.Worksheets("Details").Range("S4").End(xlDown).Row
    latestFileName = FindLatestFile(planningFolder & "\02. Field Quota Planning 2018 v*.xlsx")
    
    If latestFileName <> "" Then
        Workbooks.Open planningFolder & "\" & latestFileName
        With ThisWorkbook.Worksheets("Details")
            .Range("T4").Formula = "=INDEX('[" & latestFileName & "]All Reps'!$M:$M,MATCH(K4,'[" & latestFileName & "]All Reps'!$A:$A,0))"
            .Range("T4").AutoFill Destination:=.Range(.Range("T4"), .Range("T" & lastRow))
        End With
    Else
        MsgBox "No files found matching '02. Field Quota Planning 2018 v*.xlsx' in " & planningFolder
    End If
    
End Sub
Please use CODE tags when posting code - click the # icon in the message editor.
 
Upvote 0
John_w said:
The Workbooks.Open makes the opened file the active workbook and your AutoFill statement uses an unqualified Range reference so it uses the active sheet in the active workbook. Try this:

Code: 
Public Sub Create_Formulas()

    Dim lastRow As Long
    Dim planningFolder As String
    Dim latestFileName As String
    
    planningFolder = "\\Slnas3\nas\lib-SL\Sales\sales_enablement\001 New Stuff\Comp Plans\2018\Quota Planning"
    lastRow = ThisWorkbook.Worksheets("Details").Range("S4").End(xlDown).Row
    latestFileName = FindLatestFile(planningFolder & "\02. Field Quota Planning 2018 v*.xlsx")
    
    If latestFileName <> "" Then
        Workbooks.Open planningFolder & "\" & latestFileName
        With ThisWorkbook.Worksheets("Details")
            .Range("T4").Formula = "=INDEX('[" & latestFileName & "]All Reps'!$M:$M,MATCH(K4,'[" & latestFileName & "]All Reps'!$A:$A,0))"
            .Range("T4").AutoFill Destination:=.Range(.Range("T4"), .Range("T" & lastRow))
        End With
    Else
        MsgBox "No files found matching '02. Field Quota Planning 2018 v*.xlsx' in " & planningFolder
    End If
    
End Sub
Please use CODE tags when posting code - click the # icon in the message editor.
Click to expand...

Thank you John W

I wish I was eloquent enough to provide my thankfulness. This site and people like you is why the internet is awesome.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Take screenshots & simultaneously capture the link, place it into an Excel file which is linked to a FileMaker/Claris db.
Replies
3
Views
389
Dan_W
D
spacecaptainsuperguy
  • Question Question
Advice for linked files on network drive
Replies
0
Views
741
spacecaptainsuperguy
spacecaptainsuperguy
A
  • Solved
Comparing two excel files for matching / mismatching rows (long text strings - match() wont work
Replies
4
Views
962
ahaye
A
K
  • Solved
VBA to Open a file with variable name and version
2 3
Replies
20
Views
4K
Gokhan Aycan
Gokhan Aycan
G
  • Question Question
Select folder, unformat & clean up certain sheets within workbooks & consolidate those certain sheets into a single workbook
Replies
0
Views
714
griff78
G

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top