Opening latest version of file and then doing vlookup in that file

Hi All,

I want to open the latest version of a file and then do a vlookup in that latest version.

I know how to open the latest file, but once it is open, how do I do a vlookup without specifying that file name? So that I don't have to change the filename in the vlookup all the time?

eg. if VS_10.12.2012.xlsx is the latest version, I want to vlookup in range C10:C20 on tab VS_1.

A new version of the file will be created every week, but the tabs and cells will all remain as they are.

Your help much appreciated!!!

Regards

Thanks Norie,

Surely if one can ask Excel to open the latest version, you could allocate a reference in the code to that file that was just opened to let Excel identify that file as the one to look in?

And what would the code you referred to look like?

Thanks!

Yeah it's just that last formula. I'm looking at the lower and higher value next up in a table.

eg. if the current value is 75% the lower value could be 70% and the higher value 80% in a table in the latest file

Thanks Norie,

That seems to work - but how do I place the formula in the cell I want? ie. what do I put in place of ThisWorkbook.?

Regards

Have tried:

strFormula = "=INDEX('[" & wbVS.Name & "]VS UK'!$C$27:$C$39,MATCH(VLOOKUP(VLOOKUP(D12&E12,'2013'!$G$3:$J$119,4,FALSE),'" & wbVS.Name & "]VS UK'!$C$27:$C$39,1),'" & wbVS.Name & "]VS UK'!$C$27:$C$39,0)+1)"

Workbooks(<2013 Shareshift_Yield Preferences.xlsm>).Worksheets("UK").Range("G12").Formula = strFormula

And it doesn't seem to work..

Tried that, unfortunately doesn't work ;(

It says " Runtime Error '1004' "

Here is the total coding I have thus far, all works okay until the last line:

Sub Update2()


Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
Application.ScreenUpdating = False

Msg = "This Macro Updates Information in Your File."
Msg = Msg & vbCrLf & vbCrLf & "Are you Ready to Continue?"
DialogStyle = vbYesNo + vbExclamation + vbDefaultButton2
Title = "File Update"
Response = MsgBox(Msg, DialogStyle, Title)
If Response = vbYes Then
Else
End
End If



'Sets the MacroFrance file as MacroFile to be later activated throughout the macro
Set MacroFile = ActiveWorkbook


Dim FileSys As FileSystemObject
Dim objFile As File
Dim myFolder
Dim strFilename As String
Dim dteFile As Date

'set path for files - change for your folder
Const myDir As String = "H:\Trackers\2012\VS\"

'set up filesys objects
Set FileSys = New FileSystemObject
Set myFolder = FileSys.GetFolder(myDir)


'loop through each file and get date last modified. If largest date then store Filename
dteFile = DateSerial(1900, 1, 1)
For Each objFile In myFolder.Files
If objFile.DateLastModified > dteFile Then
dteFile = objFile.DateLastModified
strFilename = objFile.Name
End If
Next objFile
Workbooks.Open myDir & "\" & strFilename

Set FileSys = Nothing
Set myFolder = Nothing

'set latest file to use formula
Set wbVS = Workbooks.Open(myDir & "\" & strFilename)

strFormula = "=INDEX('[" & wbVS.Name & "]VS UK'!$C$27:$C$39,MATCH(VLOOKUP(VLOOKUP(D12&E12,'2013'!$G$3:$J$119,4,FALSE),'" & wbVS.Name & "]VS UK'!$C$27:$C$39,1),'" & wbVS.Name & "]VS UK'!$C$27:$C$39,0)+1)"

Workbooks("2013 Shareshift_Yield Preferences.xlsm").Worksheets("UK").Range("G12").Formula = strFormula

Yes the workbook is open

Thanks again

Okay - it's working - there were some characters missing in the formula (a [ and a ')

My NEXT question is:

How to remove the static reference "2013 Shareshift......xlsx" so that if someone saves it as another filename or date it will still work?

I want to remove the reference "2013 Shareshift...etc.") and do similar as we have done below with the "VS....etc." file:

Set wbVS = Workbooks.Open(myDir & "\" & strFilename)

So...when I open the 2013 Shareshift file, I would like it to refer to itself no matter what the file name is saved as (ie. if saved to "2013 Shareshift Latest" or something) and then reopened, the formulas will still work



Replace

Haha! Sorry!

So, this is the working code thus far:

Sub Update2()


Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
Application.ScreenUpdating = False

Msg = "This Macro Updates Information in Your File."
Msg = Msg & vbCrLf & vbCrLf & "Are you Ready to Continue?"
DialogStyle = vbYesNo + vbExclamation + vbDefaultButton2
Title = "File Update"
Response = MsgBox(Msg, DialogStyle, Title)
If Response = vbYes Then
Else
End
End If



Dim FileSys As FileSystemObject
Dim objFile As File
Dim myFolder
Dim strFilename As String
Dim dteFile As Date

'set path for files - change for your folder
Const myDir As String = "H:\Trackers\2012\VS\"

'set up filesys objects
Set FileSys = New FileSystemObject
Set myFolder = FileSys.GetFolder(myDir)


'loop through each file and get date last modified. If largest date then store Filename
dteFile = DateSerial(1900, 1, 1)
For Each objFile In myFolder.Files
If objFile.DateLastModified > dteFile Then
dteFile = objFile.DateLastModified
strFilename = objFile.Name
End If
Next objFile
Workbooks.Open myDir & "\" & strFilename

Set FileSys = Nothing
Set myFolder = Nothing

'set latest file to use formula
Set wbVS = Workbooks.Open(myDir & "\" & strFilename)

strFormula = "=INDEX('[" & wbVS.Name & "]VS UK'!$C$27:$C$39,MATCH(VLOOKUP(VLOOKUP(D12&E12,'2013'!$G$3:$J$119,4,FALSE),'" & wbVS.Name & "]VS UK'!$C$27:$C$39,1),'" & wbVS.Name & "]VS UK'!$C$27:$C$39,0)+1)"

Workbooks("2013 Shareshift_Yield Preferences.xlsm").Worksheets("UK").Range("G12").Formula = strFormula

The code in bold is what I need to change similarly to that in italics:

- The bold workbook is the one that will be opened first (and saved as a new name possibly), so I would like it to rund the code even if the filename changes

Hope that was clear

Aha - changed it thank you!