PDA

View Full Version : Vlookup Macro that updates values by file by date.



jsauriol
11-28-2007, 10:20 AM
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:


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

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
11-28-2007, 10:23 AM
?

mdmackillop
11-28-2007, 12:51 PM
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

mdmackillop
11-28-2007, 01:13 PM
The whole thing!

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

jsauriol
11-30-2007, 11:49 AM
thanks!!!!