VBA code for recent file search and open with variable date and time in file name

[ruturajs7rs]

Dear all,
I am looking for a vba code which can help me find a recent file uploaded in a path for example "D:\Test\" and copy the data from the file to current workbook open. The file name contains both the date and time of upload in its name. eg- "20210126_11_03_42_Test_2" and in the file name the date and time(11_03_42)(hh_mm_ss) depends on the time of upload. I want to open the most recent file in the mentioned folder.

 

[NdNoviceHlp]

Hi ruturajs7rs and Welcome to the Board! This will find your newest file. HTH. Dave

Sub test()
Dim objfso As Object, objfolder As Object, objfile As Object
Dim NewFileName As String, NewFileDate As String, Temp As String
Set objfso = CreateObject("scripting.filesystemobject")
Set objfolder = objfso.GetFolder("D:\Test")
NewFileDate = vbNullString
For Each objfile In objfolder.Files
Temp = objfile.DateCreated
If Temp > NewFileDate Then
NewFileDate = Temp
NewFileName = objfile.Name
End If
Next objfile
MsgBox "Newest File: " & NewFileName & " created " & NewFileDate
Set objfolder = Nothing
Set objfso = Nothing
End Sub

 

[Saurabhj]

Hi,
Check below code. No need to check filename in below code.

Sub lastModifiedFile()

    Dim filePath As String, fileName As String, LatestFile As String, LatestDate As Date
    Dim lastModDate As Date

    filePath = "D:\Test\"

    fileName = Dir(filePath, vbNormal)
   
    Do While Len(fileName) > 0
        lastModDate = FileDateTime(filePath & fileName)
        If lastModDate > LatestDate Then
            LatestFile = fileName
            LatestDate = lastModDate
        End If
        fileName = Dir
    Loop

    Workbooks.Open filePath & LatestFile
End Sub

 

[ruturajs7rs]

Hi ruturajs7rs and Welcome to the Board! This will find your newest file. HTH. Dave

Sub test()
Dim objfso As Object, objfolder As Object, objfile As Object
Dim NewFileName As String, NewFileDate As String, Temp As String
Set objfso = CreateObject("scripting.filesystemobject")
Set objfolder = objfso.GetFolder("D:\Test")
NewFileDate = vbNullString
For Each objfile In objfolder.Files
Temp = objfile.DateCreated
If Temp > NewFileDate Then
NewFileDate = Temp
NewFileName = objfile.Name
End If
Next objfile
MsgBox "Newest File: " & NewFileName & " created " & NewFileDate
Set objfolder = Nothing
Set objfso = Nothing
End Sub

Thank you Dave for the code.
But with this code I am getting a msg box with the name of current file open. I think I did not cleared out some points. Sorry for that.
I would like to open the recent file with naming convention as YYYYMMDD_HH_MM_SS_Test_2
but I will be running the code in file YYYYMMDD_HH_MM_SS_Test_1
So in the folder "D:\Test\" there will be files uploaded on the basis of date and time.
E.g.- Structure of folder
20210124_10_20_30_Test_1
20210124_10_22_30_Test_2
20210125_10_19_30_Test_1
20210125_10_20_30_Test_2
20210126_10_23_30_Test_1
20210126_10_25_30_Test_2
So I will run the code in file 20210126_10_23_30_Test_1 and copy data from 20210126_10_25_30_Test_2 in new sheet in 20210126_10_23_30_Test_1

 

[NdNoviceHlp]

This should work...

Sub test()
Dim objfso As Object, objfolder As Object, objfile As Object
Dim NewFileName As String, NewFileDate As String, Temp As String
Set objfso = CreateObject("scripting.filesystemobject")
Set objfolder = objfso.GetFolder("D:\Test")
NewFileDate = vbNullString
For Each objfile In objfolder.Files
If InStr(objfile.Name, "Test_2") Then
Temp = objfile.DateCreated
If Temp > NewFileDate Then
NewFileDate = Temp
NewFileName = objfile.Name
End If
End If
Next objfile
MsgBox "Newest File: " & NewFileName & " created " & NewFileDate
Set objfolder = Nothing
Set objfso = Nothing
End Sub

Dave

 

[ruturajs7rs]

This should work...

Sub test()
Dim objfso As Object, objfolder As Object, objfile As Object
Dim NewFileName As String, NewFileDate As String, Temp As String
Set objfso = CreateObject("scripting.filesystemobject")
Set objfolder = objfso.GetFolder("D:\Test")
NewFileDate = vbNullString
For Each objfile In objfolder.Files
If InStr(objfile.Name, "Test_2") Then
Temp = objfile.DateCreated
If Temp > NewFileDate Then
NewFileDate = Temp
NewFileName = objfile.Name
End If
End If
Next objfile
MsgBox "Newest File: " & NewFileName & " created " & NewFileDate
Set objfolder = Nothing
Set objfso = Nothing
End Sub

Dave

Thank you! Dave
the code worked out well.
Is it possible that if I run the code in 20210126_10_23_30_Test_1 to get data only from 20210126_10_25_30_Test_2 and not 20210125_10_20_30_Test_2 (previous date file), in case file 20210126_10_25_30_Test_2 is not present in the folder?

 

[NdNoviceHlp]

If the file is not in the folder, where is it.... seems like a different scenario all together? Dave

 

[ruturajs7rs]

If the file is not in the folder, where is it.... seems like a different scenario all together? Dave

Well, I mean these files are generated from SAP and are to be processed by a software application and not be human. The application runs the macro in file *Test_1 everyday and collect data from other file created on that day *Test_2. If the file for current date is not created then it should not collect data from yesterday's file *Test_2

 

[NdNoviceHlp]

That's a bit more involved. This seems to work. Run Test to trial. Dave
'copy and paste in sheet code

Dim NewFileDate As String, NewFileName As String
Private Sub Test()
Dim fs As Object, f As Object, s As String

Call CheckFileDate

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(ThisWorkbook.FullName)
s = f.DateCreated
If TestDate(NewFileDate, s) < 1 Then
MsgBox "Test2 file is current! " & NewFileName
'code to proceed goes here
Else
MsgBox "Test2 file is NOT current!"
End If
End Sub

Sub CheckFileDate()
Dim objfso As Object, objfolder As Object, objfile As Object
Dim Temp As String
Set objfso = CreateObject("scripting.filesystemobject")
Set objfolder = objfso.GetFolder("D:\Test")
NewFileDate = vbNullString
For Each objfile In objfolder.Files
If InStr(objfile.Name, "Test_2") Then
Temp = objfile.DateCreated
If Temp > NewFileDate Then
NewFileDate = Temp
NewFileName = objfile.Name
End If
End If
Next objfile
'MsgBox "Newest File: " & NewFileName & " created " & NewFileDate
Set objfolder = Nothing
Set objfso = Nothing
End Sub

Function TestDate(pDate1 As String, pDate2 As String) As Long
Dim date1 As Date, date2 As Date
date1 = CDate(pDate1)
date2 = CDate(pDate2)
TestDate = DateDiff("d", date1, date2)
End Function

 

[ruturajs7rs]

That's a bit more involved. This seems to work. Run Test to trial. Dave
'copy and paste in sheet code

Dim NewFileDate As String, NewFileName As String
Private Sub Test()
Dim fs As Object, f As Object, s As String

Call CheckFileDate

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(ThisWorkbook.FullName)
s = f.DateCreated
If TestDate(NewFileDate, s) < 1 Then
MsgBox "Test2 file is current! " & NewFileName
'code to proceed goes here
Else
MsgBox "Test2 file is NOT current!"
End If
End Sub

Sub CheckFileDate()
Dim objfso As Object, objfolder As Object, objfile As Object
Dim Temp As String
Set objfso = CreateObject("scripting.filesystemobject")
Set objfolder = objfso.GetFolder("D:\Test")
NewFileDate = vbNullString
For Each objfile In objfolder.Files
If InStr(objfile.Name, "Test_2") Then
Temp = objfile.DateCreated
If Temp > NewFileDate Then
NewFileDate = Temp
NewFileName = objfile.Name
End If
End If
Next objfile
'MsgBox "Newest File: " & NewFileName & " created " & NewFileDate
Set objfolder = Nothing
Set objfso = Nothing
End Sub

Function TestDate(pDate1 As String, pDate2 As String) As Long
Dim date1 As Date, date2 As Date
date1 = CDate(pDate1)
date2 = CDate(pDate2)
TestDate = DateDiff("d", date1, date2)
End Function

Hi Dave. Thank you for your support.
But the macro finds the previous file if the current date file is not there in the folder