VBA - Open most recent file in folder

[Ananthak275]

Trying to create a VBA that opens the most recent file in folder based on name. The folder would have files in this format (seen below).. I would want it to open "FILENAME_2022-10-20 - (Published On 2022-10-20 10-09-16)". the issue is getting around the "(Published On....".

FILENAME_2022-10-20 - (Published On 2022-10-20 10-09-16)
FILENAME_2022-10-19 - (Published On 2022-10-19 09-04-55)
FILENAME_2022-10-18 - (Published On 2022-10-18 09-16-22)
FILENAME_2022-10-17 - (Published On 2022-10-17 09-17-49)

 

[DanteAmor]

Try this:
The macro asks you to select the folder with the files.

Sub open_most_recent_file()
  Dim sPath As String, sFile As String, theFile As String, psh As String
  Dim xMax As Double, sDate As Date, n As Long

  With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select Folder"
    .AllowMultiSelect = False
    If .Show <> -1 Then Exit Sub
    sPath = .SelectedItems(1) & "\"
  End With
  
  sFile = Dir(sPath & "*.*")
  Do While sFile <> ""
    n = InStr(1, sFile, "Published On")
    If n > 0 Then
      psh = Mid(sFile, n + 13, 19)
      sDate = CDate(Left(psh, 10) & " " & Replace(Right(psh, 8), "-", ":"))
      If sDate > xMax Then
        theFile = sFile
        xMax = sDate
      End If
    End If
  
    sFile = Dir()
  Loop
  
  If theFile <> "" Then
    Workbooks.Open sPath & theFile
  End If
End Sub

 

[Ananthak275]

Try this:
The macro asks you to select the folder with the files.

Sub open_most_recent_file()
  Dim sPath As String, sFile As String, theFile As String, psh As String
  Dim xMax As Double, sDate As Date, n As Long

  With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select Folder"
    .AllowMultiSelect = False
    If .Show <> -1 Then Exit Sub
    sPath = .SelectedItems(1) & "\"
  End With
 
  sFile = Dir(sPath & "*.*")
  Do While sFile <> ""
    n = InStr(1, sFile, "Published On")
    If n > 0 Then
      psh = Mid(sFile, n + 13, 19)
      sDate = CDate(Left(psh, 10) & " " & Replace(Right(psh, 8), "-", ":"))
      If sDate > xMax Then
        theFile = sFile
        xMax = sDate
      End If
    End If
 
    sFile = Dir()
  Loop
 
  If theFile <> "" Then
    Workbooks.Open sPath & theFile
  End If
End Sub

Instead of selecting the folder, can you include one where I can insert the folder path where all the files are located?

 

[DanteAmor]

can you include one where I can insert the folder path where all the files are located?

Sure, try this:

Sub open_most_recent_file()
  Dim sPath As String, sFile As String, theFile As String, psh As String
  Dim xMax As Double, sDate As Date, n As Long

  sPath = "c:\work\files\"
  
  sFile = Dir(sPath & "*.*")
  Do While sFile <> ""
    n = InStr(1, sFile, "Published On")
    If n > 0 Then
      psh = Mid(sFile, n + 13, 19)
      sDate = CDate(Left(psh, 10) & " " & Replace(Right(psh, 8), "-", ":"))
      If sDate > xMax Then
        theFile = sFile
        xMax = sDate
      End If
    End If
  
    sFile = Dir()
  Loop
  
  If theFile <> "" Then
    Workbooks.Open sPath & theFile
  End If
End Sub

 

[Ananthak275]

Sure, try this:

Sub open_most_recent_file()
  Dim sPath As String, sFile As String, theFile As String, psh As String
  Dim xMax As Double, sDate As Date, n As Long

  sPath = "c:\work\files\"
 
  sFile = Dir(sPath & "*.*")
  Do While sFile <> ""
    n = InStr(1, sFile, "Published On")
    If n > 0 Then
      psh = Mid(sFile, n + 13, 19)
      sDate = CDate(Left(psh, 10) & " " & Replace(Right(psh, 8), "-", ":"))
      If sDate > xMax Then
        theFile = sFile
        xMax = sDate
      End If
    End If
 
    sFile = Dir()
  Loop
 
  If theFile <> "" Then
    Workbooks.Open sPath & theFile
  End If
End Sub

Apologies, but the files names are;

FILENAME_2022-10-20
FILENAME_2022-10-19
FILENAME_2022-10-18
FILENAME_2022-10-17

Can you please update the code so that now it doesn't include anything right of the date? it works perfectly for the initial request.

 

[DanteAmor]

Now I don't understand what you need anymore.
Could you explain again what you need?

Each file has a creation date, last modification date and in the name of your files you have another date.
What do you want to do, you must be more specific, to understand what you want.

By the way, are they excel files?

 

[Ananthak275]

Now I don't understand what you need anymore.
Could you explain again what you need?

Each file has a creation date, last modification date and in the name of your files you have another date.
What do you want to do, you must be more specific, to understand what you want.

By the way, are they excel files?

Yes sorry, i can provide more description.

Trying to create a VBA that opens the most recent file in folder based on name. All files are in .xlsx format. An example of the folder with the file name is shown below;
FILENAME_2022-10-20
FILENAME_2022-10-19
FILENAME_2022-10-18
FILENAME_2022-10-17

is there anything else?

 

[DanteAmor]

FILENAME_2022-10-20

All files always have the word FILENAME and an underscore at the beginning of the name?

"FILENAME_"

 

[Ananthak275]

All files always have the word FILENAME and an underscore at the beginning of the name?

"FILENAME_"

Yes

 

[DanteAmor]

Try this:

Sub open_most_recent_file()
  Dim sPath As String, sFile As String, theFile As String
  Dim xMax As Double, sDate As Date

  sPath = "c:\trabajo\files\"
  
  sFile = Dir(sPath & "*.*")
  Do While sFile <> ""
    If UCase(Left(sFile, 9)) = "FILENAME_" Then
      sDate = CDate(Mid(sFile, 10, 10))
      If sDate > xMax Then
        theFile = sFile
        xMax = sDate
      End If
    End If
  
    sFile = Dir()
  Loop
  
  If theFile <> "" Then
    Workbooks.Open sPath & theFile
  End If
End Sub