Vlookup Macro that updates values by file by date.

[jsauriol]

The formula below opens a excel report that has a date on it. We run that report every couple of days so the date changes constantly. I have a macro that updates values based on new information from the other report i just spoke of. I cant seem to think of a way to code this so it just looks for the most current file. Here is what I have:

[VBA]
Application.ScreenUpdating = False
ChDir "K:\Procurement\PO DATA"
PODATAPath = Application.GetOpenFilename("(*.xls),*.xls", , "PO Data as of...")
Workbooks.Open Filename:=PODATAPath
Windows("PO Data as of 11-27-07.xls").Activate
Windows("Shipping Method Macro.xls").Activate
ActiveWindow.SmallScroll Down:=-20
Range("F7").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[PO Data as of 11-27-07.xls]MASTER'!C1:C20,20,0)"
Selection.AutoFill Destination:=Range("F7:F50"), Type:=xlFillDefault
Range("F7:F50").Select
[/VBA]
PO Data is the file that I use to update the other Report I have called Shipping Method. Shipping Method is where this code comes from. As you can see I am currently taking from "PO Data as of 11-27-07" and I need something to just vlookup from the most current file called PO Data.

I hope this makes sense to someone of what I need. I hope you guys can help.

[jsauriol]

?

[mdmackillop]

[VBA]
Sub LatestFile()
Dim fdate, tmp, fname As String, LastFile As String
tmp = 0
fname = Dir("C:\AAA\*.xls")
Do
fdate = Split(fname, "of ")(1)
fdate = CDate(Split(fdate, ".")(0))
If fdate > tmp Then
tmp = fdate
LastFile = fname
End If
MsgBox tmp
fname = Dir
Loop Until fname = ""
Workbooks.Open ("C:\AAA\" & LastFile)
End Sub

[/VBA]

[mdmackillop]

The whole thing!
[vba]
Option Explicit
Sub Test()
Dim LFile As String
Application.ScreenUpdating = False
Dim Pth As String
Pth = "K:\Procurement\PO DATA\"
LFile = LatestFile(Pth)
Workbooks.Open Pth & LFile
Windows("Shipping Method Macro.xls").Activate
With Range("F7")
.FormulaR1C1 = "=VLOOKUP(RC[-1],'[" & LFile & "]MASTER'!C1:C20,20,0)"
.AutoFill Destination:=Range("F7:F50"), Type:=xlFillDefault
End With
Range("F7:F50").Select
End Sub

Function LatestFile(Pth As String)
Dim fdate, tmp, fname As String, LastFile As String
tmp = 0
fname = Dir(Pth & "*.xls")
Do
On Error Resume Next
fdate = Split(fname, "of ")(1)
fdate = CDate(Split(fdate, ".")(0))
If fdate > tmp Then
tmp = fdate
LastFile = fname
End If
fname = Dir
Loop Until fname = ""
LatestFile = LastFile
End Function


[/vba]

[jsauriol]

thanks!!!!