List details of all the files within a folder using VBA in Microsoft Excel

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.

ArrowMain

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.

ArrowOutput

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

Users are saying about us...

  1. 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 ?

  2. 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?

  3. “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

  4. “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”

  5. 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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube