Update Spreadsheet when open

[josros60]

Hi,

how can i modify the code below that when spreadsheet open update automatically.

Sub ListFilesInFolder(ByVal SourceFolderName As String, ByVal IncludeSubfolders As Boolean)

'Declaring variables
Dim FSO As Object
Dim SourceFolder As Object
Dim SubFolder As Object
Dim FileItem As Object
Dim r As Long
   
'Creating object of FileSystemObject
Set FSO = CreateObject("Scripting.FileSystemObject")
Set SourceFolder = FSO.GetFolder(SourceFolderName)

r = Range("A65536").End(xlUp).Row + 1

For Each FileItem In SourceFolder.Files

    'Display file properties
     Cells(r, 1).Formula = FileItem.Name
     Cells(r, 2).Formula = FileItem.Path
     'Cells(r, 3).Formula = FileItem.Size
     Cells(r, 3).Formula = FileItem.DateCreated
     Cells(r, 4).Formula = FileItem.DateLastModified
     Cells(r, 5).Select
                    Selection.Hyperlinks.Add Anchor:=Selection, Address:= _
                    FileItem.Path, TextToDisplay:="Click Here to Open"
     
     r = r + 1
     
Next FileItem

'Getting files in sub folders
If IncludeSubfolders Then
     For Each SubFolder In SourceFolder.SubFolders
        'Calling same procedure for sub folders
        ListFilesInFolder SubFolder.Path, True
     Next SubFolder
End If

Set FileItem = Nothing
Set SourceFolder = Nothing
Set FSO = Nothing

ActiveWorkbook.Saved = True

End Sub


Sub TestListFilesInFolder()

'Declaring variable
Dim FolderPath As String

'Disabling screen updates
Application.ScreenUpdating = False

'Getting the folder path from text box
FolderPath = Sheet1.TextBox1.Value

ActiveSheet.Activate

'Clearing the content from columns A:E
Columns("A:E").Select
Selection.ClearContents


'Adding headers
Range("A7").Formula = "File Name:"
Range("B7").Formula = "Path:"
'Range("C9").Formula = "File Size:"
Range("C7").Formula = "Date Created:"
Range("D7").Formula = "Date Last Modified:"
Range("E7").Formula = "Select File"
'Formating of the headers
Range("A7:E7").Font.Bold = True

'Calling ListFilesInFolder macro
ListFilesInFolder FolderPath, True

'Auto adjusting the size of the columns
Columns("A:E").Select
Selection.Columns.AutoFit

Range("A1").Select

Thank you,

 

[Logit]

In the ThisWorkbook module, include this small macro :

Sub RunSubOnOpen ()


     Call TestListFilesInFolder


End Sub

 

[josros60]

thank you, worked.

is it possible after the update also to sort the records by the date column?