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]

Hmmm..... so the previous file is less than 1 day old? Dave

 

[ruturajs7rs]

Hmmm..... so the previous file is less than 1 day old? Dave

No, it was created 6 days ago

 

[NdNoviceHlp]

My testing again seemed to work. Here's a stripped down version for testing. Dave

Sub CheckFileDate()
Dim objfso As Object, objfolder As Object, objfile As Object, F As Object
Dim Temp As String, s As String, TestD As String
Dim NewFileDate As String, NewFileName 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 F = objfso.GetFile(ThisWorkbook.FullName)
s = F.DateCreated
MsgBox "This file creation date: " & s
TestD = DateDiff("d", CDate(NewFileDate), CDate(s))
MsgBox "Time interval: " & TestD & " days"
Set F = Nothing
Set objfolder = Nothing
Set objfso = Nothing
End Sub

 

[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

Worked now. I don't know how?
Thank you very much

 

[NdNoviceHlp]

You are again welcome. Thanks for posting your outcome. Be safe. Dave

 

[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
This code won't work as it is considering only the date on which I first ran this Macro in Personal Excel workbook.
Is it required to adapt the code to run Personal Macroworkbook?