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



4 thoughts on “List details of all the files within a folder using VBA in Microsoft Excel

  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”

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>