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

ruturajs7rs

New Member
Joined
Jan 26, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,776
Hi ruturajs7rs and Welcome to the Board! This will find your newest file. HTH. Dave
Code:
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

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,
Check below code. No need to check filename in below code.

VBA 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

New Member
Joined
Jan 26, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hi ruturajs7rs and Welcome to the Board! This will find your newest file. HTH. Dave
Code:
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

Well-known Member
Joined
Nov 9, 2002
Messages
2,776

ADVERTISEMENT

This should work...
Code:
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

New Member
Joined
Jan 26, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
This should work...
Code:
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

Well-known Member
Joined
Nov 9, 2002
Messages
2,776

ADVERTISEMENT

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

ruturajs7rs

New Member
Joined
Jan 26, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 9, 2002
Messages
2,776
That's a bit more involved. This seems to work. Run Test to trial. Dave
'copy and paste in sheet code
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
 
Solution

ruturajs7rs

New Member
Joined
Jan 26, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
That's a bit more involved. This seems to work. Run Test to trial. Dave
'copy and paste in sheet code
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,462
Messages
5,636,419
Members
416,917
Latest member
koto1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top