Create list of files in a folder and link to them in Excel

[Khimono]

Hi all,

I'm looking for some help finishing the code below. It's a macro to populate a list of files in a folder, currently W:\test and automatically hyperlink to the files. It's working as intended at the moment, apart from one issue I can't solve.

It automatically populates the list in alphabetical order. I'd like the list to generate in order by date created, with the newest file at the top of the list.

Could someone help me modify the code below to solve this?

Thanks


Sub Memo()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("W:\test")
i = 1
For Each objFile In objFolder.Files


Range(Cells(i + 11, 5), Cells(i + 11, 5)).Select

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
objFile.Path, _
TextToDisplay:=objFile.Name
i = i + 1
Next objFile

End Sub

[mdmackillop]

Welcome to VBAX


Option Explicit
Sub Memo()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Long
Dim c As Range

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("W:\test")
Set c = Cells(12, 5)
For Each objFile In objFolder.Files
ActiveSheet.Hyperlinks.Add Anchor:=c.Offset(i), Address:= _
objFile.Path, _
TextToDisplay:=objFile.Name
c.Offset(i, 1) = objFile.datecreated
i = i + 1
Next objFile
With c.Resize(i, 2)
.Sort key1:=c.Columns(2), order1:=xlDescending, Header:=xlNo
'.Columns(2).ClearContents 'Check
End With
End Sub

[Khimono]

Welcome to VBAX


Option Explicit
Sub Memo()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Long
Dim c As Range

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("W:\test")
Set c = Cells(12, 5)
For Each objFile In objFolder.Files
ActiveSheet.Hyperlinks.Add Anchor:=c.Offset(i), Address:= _
objFile.Path, _
TextToDisplay:=objFile.Name
c.Offset(i, 1) = objFile.datecreated
i = i + 1
Next objFile
With c.Resize(i, 2)
.Sort key1:=c.Columns(2), order1:=xlDescending, Header:=xlNo
'.Columns(2).ClearContents 'Check
End With
End Sub



This is fantastic! Worked like a charm. Thank you very much for your help.