Update Spreadsheet when open

josros60

Well-known Member
Joined
Jun 27, 2010
Messages
635
Office Version
  1. 365
Hi,

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

VBA Code:
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,
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,301
In the ThisWorkbook module, include this small macro :

VBA Code:
Sub RunSubOnOpen ()


     Call TestListFilesInFolder


End Sub
 

josros60

Well-known Member
Joined
Jun 27, 2010
Messages
635
Office Version
  1. 365
thank you, worked.

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

Forum statistics

Threads
1,148,157
Messages
5,745,105
Members
423,923
Latest member
yuvaraj859

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top