Open the 5 most recently saved .txt files

[jfarc]

Not sure where to start with this.

I want to open in Excel the 5 most recently saved .txt files in a particular folder (C:\MyFolder). I'd like to open them in order starting with the newest file found and working through to the 5th file(if present).

I will process each file after each is opened - this I can handle.

 

[Cindy Ellis]

Give this a try...

Option Explicit
Sub List5New()

Dim Fs      As Object   'FileSystem
Dim SD      As String   'directory name
Dim D       As Object   'Folder object
Dim File    As Object   'File
Dim GoAhead As Integer
Dim Newest5(4) As String 'change the (4) to (5) if you use option base 1.  The default is option base 0

Dim iRow As Long 'keeps track of rows in the file list
Dim j As Integer

GoAhead = MsgBox("If the workbook includes a worksheet named TempFileList it will be deleted.  Do you want to continue?", vbYesNo)
If GoAhead = vbNo Then Exit Sub

On Error Resume Next 'deletes a previous file list if it exists, without crashing if it's not there
Application.DisplayAlerts = False
Sheets("TempFileList").Delete
Application.DisplayAlerts = True
On Error GoTo 0 'restores normal error handling

SD = "C:\MyFolder"
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "TempFileList"
  
    iRow = 1
    Set Fs = CreateObject("Scripting.FileSystemObject")
    Set D = Fs.GetFolder(SD)
    
    For Each File In D.Files
    If Right(File.Name, 3) = "txt" Then
        Cells(iRow, 1).Value = File.Name
        Cells(iRow, 2).Value = File.DateLastModified
        iRow = iRow + 1
    End If
    Next File
    
    ActiveWorkbook.Worksheets("TempFileList").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("TempFileList").Sort.SortFields.Add Key:=Range("B1" _
        ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("TempFileList").Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("B1"), Order:=xlDescending
        .SetRange Range("A:B")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
    
    'this puts the filenames into an array.  You can leave them in the worksheet if it's more convenient for you
    For j = 0 To 4
        Newest5(i) = ActiveWorkbook.Worksheets("TempFileList").Cells(j + 1, 1).Value
    Next j

    'if you want to reference the filenames from the worksheet, un-comment the next 3 lines of code
    
    Application.DisplayAlerts = False
    Sheets("TempFileList").Delete
    Application.DisplayAlerts = True
    
    'put your code here instead of the msgbox!

    MsgBox (Newest5(0) & Chr(13) & Newest5(1) & Chr(13) & Newest5(2) & Chr(13) & Newest5(3) & Chr(13) & Newest5(4))
  
End Sub

Hope this helps,

Cindy

 

[repairman615]

Hello Cindy,

Seen a small typo:


Change i in this line

Newest5(i) = ActiveWorkbook.Worksheets("TempFileList").Cells(j + 1, 1).Value

to j

Newest5(j) = ActiveWorkbook.Worksheets("TempFileList").Cells(j + 1, 1).Value

Also here is an attempt to open each file (not tested)

<font face=Courier New>    <SPAN style="color:#00007F">For</SPAN> j = 0 <SPAN style="color:#00007F">To</SPAN> 4<br>        Newest5(j) = ActiveWorkbook.Worksheets("TempFileList").Cells(j + 1, 1).Value<br>        Shell "notepad.exe " & SD & Newest5(j), vbMaximizedFocus <SPAN style="color:#007F00">' open a txt document</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> j</FONT>

 

[Cindy Ellis]

Thanks! (That's why you're the repairman?)
I tested and fixed it on my system after I pasted into the dialog, but forgot to paste back the fixed code!!
Cindy

 

[repairman615]

Thanks! (That's why you're the repairman?)
I tested and fixed it on my system after I pasted into the dialog, but forgot to paste back the fixed code!!
Cindy

More like repair - student...
Even you have helped in my VBA!

Thanks!

 

[jfarc]

That's just beautiful, Cindy! Thank you.

(I couldn't have done better myself!)

Just another note: I 'commented' rather than 'un-comment' the next 3 lines to reference the filenames in the worksheet.

 

[Cindy Ellis]

That's just beautiful, Cindy! Thank you. .

Thanks for the feedback!

Just another note: I 'commented' rather than 'un-comment' the next 3 lines to reference the filenames in the worksheet.

oops...yes, you're right.