VBA - Listing Files from a Folder

[Frank M]

I've written a Macro (see attached file) that lists all files in a folder to an Excel Sheet
(works fine).

What I want to do now is modify the Macro to list just the last 10 files in the folder.

Thanks Frank.

 

[Luke M]

What counts as "last 10"? Last 10...alphabetically? By date? By size?

 

[Frank M]

What counts as "last 10"? Last 10...alphabetically? By date? By size?

I worked on my macro this weekend, and with the help of Google searches was able to modify my macro that now uses an internal bubble sort to accomplish what I wanted.

Thanks for your interest.

 

[Luke M]

You're welcome. Would be nice to post your code so that others can benefit from what you've learned.

 

[Frank M]

You're welcome. Would be nice to post your code so that others can benefit from what you've learned.

Great Idea. See MacroSort (attached).

 

[Smallman]

Hi Frank

Thanks for posting the code. It should help others following your path. It might be worth adding that you need to add a reference to Microsoft Scripting Runtime in order for your code to work.

• To reference this file, load the Visual Basic Editor (ALT-F11)
• Select Tools - References from the drop-down menu
• A listbox of available references will be displayed
• Tick the check-box next to 'Microsoft Scripting Runtime'
• The full name and path of the scrrun.dll file will be displayed below the listbox
• Click on the OK button

I have had a play around with it and here is my take on the problem.

Sub GetDateCreated()
    Const sPath = "C:\Users\HYMC\Excel\Helping\" 'Change to suit
    Dim oFS As Object
    Dim i As Integer
    Dim sFil As String

    sFil = Dir(sPath & "*.xl*") 'xl here adds flexibility
    Set oFS = CreateObject("Scripting.FileSystemObject")

    Do While sFil <> ""
        Range("A65536").End(xlUp)(2).Value = sPath & sFil
        sFil = Dir
    Loop
    'List the Created Date
    For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
        Range("B" & i) = oFS.GetFile(Range("A" & i)).DateCreated
    Next i
    Range("A2", Range("B" & Rows.Count).End(xlUp)).Sort [b2], 2
    [A12:B400].ClearContents
End Sub

Change the file path to suit. It assumes you won't have more than 410 files in a folder. See last line of code.

It simply lists the XL files in a directory then sorts them by date removing everything after the 10th date.

Will upload a file to show workings.

Take care

Smallman

 

[Frank M]

On 9-29-14, I posted the code for my MacroSort. Thanks to a reply from Smallman, he reminded me that you will need to do the following (1 time only) to execute this macro correctly.

• Load the Visual Basic Editor (ALT-F11)

• Select Tools - References from the drop-down menu

• A listbox of available references will be displayed

• Tick the check-box next to 'Microsoft Scripting Runtime'

• The full name and path of the scrrun.dll file will be displayed below the listbox

• Click on the OK button