In this article, we will create a macro to gather details of all the files within a folder.
Before running the macro, we need to specify the path of the folder in the text box.
On running the macro, it will return File name, File path, File size, Date created and Date last modified of all the files within the folder.
Logic explanation
In this article, we have created two macros “ListFilesInFolder” and “TestListFilesInFolder”.
“ListFilesInFolder” macro will display details related to all the files within the folder.
“TestListFilesInFolder” macro is used to specify the header and call “ListFilesInFolder” macro.
Code explanation
Set FSO = CreateObject("Scripting.FileSystemObject")
The above code is used to create a new object of file system object.
Set SourceFolder = FSO.GetFolder(SourceFolderName)
The above code is used to create an object of the folder specified by the path.
Cells(r, 1).Formula = FileItem.Name
Cells(r, 2).Formula = FileItem.Path
Cells(r, 3).Formula = FileItem.Size
Cells(r, 4).Formula = FileItem.DateCreated
Cells(r, 5).Formula = FileItem.DateLastModified
The above code is used to extract details of the files.
For Each SubFolder In SourceFolder.SubFolders
'Calling same procedure for sub folders
ListFilesInFolder SubFolder.Path, True
Next SubFolder
The above code is used to extract details of all the files within sub-folders.
Columns("A:E").Select
Selection.ClearContents
The above code is used to delete content from column A to E.
Please follow below for the code
Option Explicit
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, 4).Formula = FileItem.DateCreated
Cells(r, 5).Formula = FileItem.DateLastModified
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("A14").Formula = "File Name:"
Range("B14").Formula = "Path:"
Range("C14").Formula = "File Size:"
Range("D14").Formula = "Date Created:"
Range("E14").Formula = "Date Last Modified:"
'Formating of the headers
Range("A14:E14").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
End Sub
If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
With the help of your above-mentioned code, I have created a macro to find the file modified date of a file.
But for this line of code,(If Format(FileItem.DateLastModified, "dd-mm-yyyy") Format(Now(), "dd-mm-yyyy") Then), I am getting a run time error 438. - Object doesn't support this property or method.
Please guide me as to how the above code should be modified?
'===========================================================
Option Explicit
Sub Find_date_modified_of_a_file()
'Declaring variables
Dim FSO As Object
Dim SourceFolder As Object
Dim FileItem As Object
Dim FolderPath As String
Dim Filename As String
Dim dt1 As Variant, dt2 As Variant, dt3 As Date, dt4 As Date
dt1 = Now()
dt2 = Format(dt1, "dd-mm-yyyy")
FolderPath = "D:\today\"
Filename = "CCSL-WSG.xlsx"
Workbooks.Open Filename:="D:\Today\CCSL-WSG.xlsx"
'Creating object of FileSystemObject
Set FSO = CreateObject("Scripting.FileSystemObject")
'Set SourceFolder = FSO.GetFolder(SourceFolderName)
'Set SourceFolder = FSO.GetFolder("D:\today\")
Set SourceFolder = FSO.GetFolder(FolderPath)
'Set FileItem = FSO.GetFile("CCSL-WSG.xlsx")
'Set FileItem = FSO.GetFile(Filename)
Set FileItem = ActiveWorkbook
With FileItem
dt3 = FileItem.DateLastModified
dt4 = Format(dt3, "dd-mm-yyyy")
'If Format(dt3, "dd-mm-yyyy") Format(Now(), "dd-mm-yyyy") Then
'If dt4 dt2 Then
If Format(FileItem.DateLastModified, "dd-mm-yyyy") Format(Now(), "dd-mm-yyyy") Then
MsgBox ("CCSL File is a old file. Please copy today's CCSL file to 'D:\Today'.")
Application.DisplayAlerts = False
Workbooks("CCSL-WSG.xlsx").Close SaveChanges:=False
'GoTo Protecting_sheets
End If
End With
'Application.DisplayAlerts = True
End Sub
"The code below lists the file contents of the current folder in the form of a full path. It needs to be modified t so it displays the DateCreated (date only) information of the file in column B. Please help!
Sub IndexFiles()
Sheets.Add
ActiveSheet.Name = ""Index""
With Application.FileSearch
' If you leave the lookin line out it will display this
' list for the current active directory..LookIn = ""U:\scarab\production information""
.FileType = msoFileTypeAllFiles
.SearchSubFolders = True
.Execute
End With
cnt = Application.FileSearch.FoundFiles.Count
For i = 1 To cnt
rng = ""A"" & i
Range(rng).Value = Application.FileSearch.FoundFiles.Item(i)
Next i
End Sub"
"The simplest solution is to use the built in dialogbox and let the user select multiple files (ctrl-click or shift+click):
arrFiles = Application.GetOpenFilename(""All files,*.*"", 1, ""Select"", , True) If you really want to create the solution you ask for, you have to add a listbox to a userform, set the property ListStyle to fmListStyleOption and the property MultiSelect to fmMultiSelectMulti. Instead of writing the file names to a worksheet as the example above does, you have to add them to the listbox using a loop, e.g. in the userform initialize event procedure, like this:
Me.ListBox1.AddItem FileItem.Path & FileItem.Name
"
Can this code be modified to include a prompt for the user to select the directory and instead of creating a new workbook, display the contents on the active sheet?
How would one modify the code to list the files on a form, and then to place a check box next to each file, so that the user may select the file they wish to use ?