Vlookup with Date Changes daily

[mwalls33]

I currently have this macro:

Dim Lr As Long

Lr = Range("E" & Rows.Count).End(xlUp).Row
Range("P2").Select
Range("P2:P" & Lr).FormulaR1C1 = _
"=IF(VLOOKUP(RC[-9],'[IPNS Security 07092018.xlsx]Sheet1'!C8,1,FALSE),""ARGUS"")"

but the file name here the date "07092018" changes. Is there anyway for vlookup the most recent available file to look up to?

I have the below code to find the workbook, I need to use the whatever file below finds.

Sub Open_Argus()
'
' Open Latest Argus File


'---Opens a sheet based on date, searches backward from today til it finds a matching date
Dim dtTestDate As Date
Dim sStartWB As String

Const sPath As String = "V:\Security\ARGUS\Argus Users Lists\2018"
dtEarliest = Date - 5
'--to stop loop if file not found by earliest valid date.
dtTestDate = Date
sStartWB = ActiveWorkbook.Name

While ActiveWorkbook.Name = sStartWB And dtTestDate >= dtEarliest
On Error Resume Next
Workbooks.Open sPath & "IPNS Security " & Format(dtTestDate, "MMDDYYYY") & ".xlsx"
dtTestDate = dtTestDate - 1
On Error GoTo 0
Wend

If ActiveWorkbook.Name = sStartWB Then MsgBox "Earlier file not found."

 

[WaterGypsy]

This is the code I use. It relies on the date being in format yyyymmdd at the end of the file name (the leading part of the filename is in sFilePref) and the name of the file is returned in sResult, sPath is the full path, sOption is "LatestDate" 'cos I intended to give other options but haven't done that yet

Sub FindFile(sPath As String, sFilePref As String, sOption As String, sResult As String)

Dim sLatestDate As String
Dim sFileFound As String
Dim sTempDate As String
sLatestDate = ""
sResult = ""
sFileFound = Dir(sPath)
While sFileFound <> ""
If Left(sFileFound, Len(sFilePref)) = sFilePref Then
If sOption = "LatestDate" Then
sTempDate = Mid(sFileFound, Len(sFilePref) + 1, 8)
If sTempDate > sLatestDate Then
sLatestDate = sTempDate
sResult = sFileFound
End If
End If
End If
sFileFound = Dir()
Wend
End Sub

 

[mwalls33]

This is the code I use. It relies on the date being in format yyyymmdd at the end of the file name (the leading part of the filename is in sFilePref) and the name of the file is returned in sResult, sPath is the full path, sOption is "LatestDate" 'cos I intended to give other options but haven't done that yet

Sub FindFile(sPath As String, sFilePref As String, sOption As String, sResult As String)

Dim sLatestDate As String
Dim sFileFound As String
Dim sTempDate As String
sLatestDate = ""
sResult = ""
sFileFound = Dir(sPath)
While sFileFound <> ""
If Left(sFileFound, Len(sFilePref)) = sFilePref Then
If sOption = "LatestDate" Then
sTempDate = Mid(sFileFound, Len(sFilePref) + 1, 8)
If sTempDate > sLatestDate Then
sLatestDate = sTempDate
sResult = sFileFound
End If
End If
End If
sFileFound = Dir()
Wend
End Sub

Thank you so much for your response! I'm not that great in Excel so the code you gave can I just copy and paste this to what I have

 

[WaterGypsy]

Thank you so much for your response! I'm not that great in Excel so the code you gave can I just copy and paste this to what I have

Sorry.. just back in the office.

You'll need to call the subroutine with lines like this:

sLookupPath = " your path "
sLookupFile = "your fie name before the date "
sLookupOption = "LatestDate"
sLookupResult = ""
Call FindFile(sLookupPath, sLookupFile, sLookupOption, sLookupResult)

then sLookupResult will contain the date of the latest file

 

[mwalls33]

I'm sorry here are my script - what do I need to add or change to get this resolve? I want the macro to change the date of the filename based from which file the first macro opens?

Sub OpenPW_A()
'
' OpenPW_A Macro
'

'
Dim curDate As String, Fname As String
curDate = Format(Date, "mmddyyyy")
Dim wb As Workbook

Fname = _
"V:\Security\IIQ\IIQ - Advanced Analytics\IIQ All HR Identities\IIQ - Active Identities " & curDate & ".csv"

Set wb = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, Notify:=False)
Sub Get_Password()
'
' Get_Password Macro
' if error - ask user to change the date
Dim Lr As Long
Lr = Range("D" & Rows.Count).End(xlUp).Row
Application.WindowState = xlNormal
Windows("Prep File.xlsx").Activate
Range("F2:F" & Lr).FormulaR1C1 = _
"=VLOOKUP(RC[-2],'IIQ - Active Identities 07192018.csv'!C9:C10,2,FALSE)"

End Sub

 

[mwalls33]

Will anyone else able to help? thanks!