Open the latest file in a folder, and the second latest - up till the tenth latest saved

[nikolajfh]

Hi guys.

I have a problem that I really hope somebody can solve.

I need a macro that can open 1 of the 10 latest saved files (XLS, XlSX, XLSM) in a folder.

For example if I need to open the third last file, I would like to able to write that or choose a cell in the workbook wich would give me the information, so the macro knows wich file it should take.

I have the macro for opening the latest saved file, which I search the internet for. But I cant find anything to solve my current problem.
open-the-latest-file-in-a-folder.xlsm

I hope it's possible and someone can help me

[patel45]

Sub morerecent()
R = 1
Range("A1:A100").ClearContents
Cells(R, 1).Value = "File Name"
Cells(R, 2).Value = "DateLastModified"
fpath = "E:\test\"   '<<<<<<<<<< to be changed
Call ShowList(fpath, R + 1, 1)
LR = Cells(Rows.count, "A").End(xlUp).Row
Range("A2:B" & LR).Sort key1:=Range("B2"), order1:=xlDescending, Header:=xlNo, DataOption1:=xlSortTextAsNumbers
Set mybook = Workbooks.Open(fpath & Range("A4").Text) ' opens 3°
End Sub

Sub ShowList(fpath, arow, col)
        Dim fs, f, f1, s, sf
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set f = fs.GetFolder(fpath)
        Set NFile = f.Files
        For Each pf1 In NFile
            If pf1.Name = "" Then Exit Sub
            Cells(arow, col) = pf1.Name
            Cells(arow, col + 1) = pf1.DateLastModified
            arow = arow + 1
        Next
End Sub

[patel45]

Sub morerecent()
R = 1
Range("A1:A100").ClearContents
Cells(R, 1).Value = "File Name"
Cells(R, 2).Value = "DateLastModified"
fpath = "E:\test\"   '<<<<<<<<<< to be changed
Call ShowList(fpath, R + 1, 1)
LR = Cells(Rows.count, "A").End(xlUp).Row
Range("A2:B" & LR).Sort key1:=Range("B2"), order1:=xlDescending, Header:=xlNo, DataOption1:=xlSortTextAsNumbers
numfile=3
Set mybook = Workbooks.Open(fpath & Range("A" & numfile +1).Text) ' opens 3°
'numfile=5
'Set mybook = Workbooks.Open(fpath & Range("A" & numfile +1).Text) ' opens 5°
End Sub

[nikolajfh]

Thank you so much, this works! I just have a problem changing wich file to open, how do you chance from 3 to 5 or 7?

[nikolajfh]

Thank you!
There is still a little problem. It can not open from other months than the current.

I have a folder with files from every week: week1.xls -> week24.xls

If i change the numfile to 5 or 10, it just takes the first file from this month week23.xls.

But it works from numfile 1 to 3, I just need to go further back.

[patel45]

it's not very clear for me

[nikolajfh]

If you have a folder with files from previous months, you can try it out and see?

[patel45]

I think so

[nikolajfh]

If set numfile to 5, it will not take the file that is the fifth newest. it takes the first file from this month.

[guydixon]

Hi There,

Hoping that anyone can help me

I'm trying to extract the second latest file within a folder, without generating the two lists as the script above does.

I'd just like the macro to open the second latest file - can this be done?

Many thanks!
Guy

[Leith Ross]

Hello guydixon,

In the future, please start a new thread. You are not supposed to post a new question in another person's thread. Include a link to the post in your question.

Here is a macro to extract the latest Excel files (.xls, .xlsb, .xlsm, ..xlsx) from a folder. There is an example macro to open the second latest modified Excel file in the folder.

' Written: Novemebr 28, 2013
' Author:  Leith Ross
' Summary: Return a sorted array of files matching a given pattern from a specified folder.

Function GetFiles(ByVal FilePath As Variant, ByVal FileFilter As String, Optional ByVal SortOrder As Long) As Variant

  ' Return an array of files matching the filter and sorted by date last modified.
  ' The array is a zero based n x 1 array. First file name is at (0, 0), the date is at (0, 1).
  ' SortOrder = xlAscending or xlDescending
  ' Default sort order is ascending order (A to Z).
  
    Dim FileList    As Variant
    Dim J           As Long
    Dim n           As Long
    Dim oFile       As Object
    Dim oFiles      As Object
    Dim oFolder     As Object
    Dim oShell      As Object
    Dim Sorted      As Boolean
    Dim UB          As Long
    
        
        Set oShell = CreateObject("Shell.Application")
        Set oFolder = oShell.Namespace(FilePath)
        
        If oFolder Is Nothing Then
            MsgBox "Folder Not Found!" & vbCrLf & vbCrLf & "Please check the path is correct." & vbCrLf & FilePath, vbExclamation + vbOKOnly
            Exit Function
        End If
        
            Set oFiles = oFolder.Items
            oFiles.Filter 64, FileFilter
            
            If oFiles Is Nothing Then
                MsgBox "No matching files were found.", vbExclamation + vbOKOnly
                Exit Function
            End If
            
            ReDim FileList(oFiles.Count - 1, 1)
            
            For Each oFile In oFiles
                FileList(n, 0) = oFile.Name
                FileList(n, 1) = CDate(oFolder.GetDetailsOf(oFile, 3))  ' Date Last Modified
                n = n + 1
            Next oFile
        
              ' Sort the files using a modified bubble sort.
                UB = UBound(FileList)
        
                Do
                    Sorted = True
                
                    For J = 0 To UB - 1
                        If (SortOrder = xlDescending) Xor (FileList(J, 1) > FileList(J + 1, 1)) Then
                            Temp = FileList(J + 1, 0)
                            FileList(J + 1, 0) = FileList(J, 0)
                            FileList(J, 0) = Temp
                        
                            Temp = FileList(J + 1, 1)
                            FileList(J + 1, 1) = FileList(J, 1)
                            FileList(J, 1) = Temp
                        
                            Sorted = False
                        End If
                    Next J
                
                    UB = UB - 1
                Loop Until Sorted Or UB < 1
            
        GetFiles = FileList
        
End Function

Example to Open the Second Latest File

Sub Macro1()

    Dim FilePath    As String
    Dim WkbFile     As String
    
        FilePath = "C:\Users\Owner\Documents"
        
      ' Get the second lastest modified workbook.
        WkbFile = FilePath & "\" & GetFiles(FilePath, "*.xls*", xlDescending)(1, 0)
    
        Workbooks.Open (WkbFile)
    
End Sub