In this post, I will show you how to get and display the list of files in a folder and display them in an Excel spreadsheet. The below picture show the output spreadsheet of this procedure which display the files in a folder named c:\intel.

Note that you may encounter undefined object errors during the execution of below procedure. To fix this, you only need to enable a reference to Microsoft Scripting Runtime in Visual Basic Editor (Select Tools - References )

Sub CallListDir()
ListDir "C:\Intel", True
End Sub


Function ListDir(Folder As String, IncludeSubFolders As Boolean)

Dim FSO As Scripting.FileSystemObject
Dim CurFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim CurItem As Scripting.File
Dim RowNum As Long

Set FSO = New Scripting.FileSystemObject
Set CurFolder = FSO.GetFolder(Folder)
RowNum = Range("A65536").End(xlUp).Row

Cells(RowNum, 1).Formula = "FileName"
Cells(RowNum, 2).Formula = "FileSize"
Cells(RowNum, 3).Formula = "FileType"
Cells(RowNum, 4).Formula = "FileCreatedDate"
Cells(RowNum, 5).Formula = "FileLastAccessedDate"
Cells(RowNum, 6).Formula = "FileDateLastModifiedDate"
Cells(RowNum, 7).Formula = "FileAttributes : (R)eadOnly/(H)idden/(S)ystem/(V)olume/(D)irectory/(A)rchive/(A)lias/(C)ompressed"
RowNum = RowNum + 1

For Each CurItem In CurFolder.Files

Cells(RowNum, 1).Formula = CurItem.Path '& CurItem.Name
Cells(RowNum, 2).Formula = CurItem.Size
Cells(RowNum, 3).Formula = CurItem.Type
Cells(RowNum, 4).Formula = CurItem.DateCreated
Cells(RowNum, 5).Formula = CurItem.DateLastAccessed
Cells(RowNum, 6).Formula = CurItem.DateLastModified
Cells(RowNum, 7).Formula = ListFileAttr(CurItem.Attributes)
RowNum = RowNum + 1
Next CurItem
If IncludeSubFolders Then
For Each SubFolder In CurFolder.SubFolders
ListDir SubFolder.Path, True
Next SubFolder
End If
Columns("A:H").AutoFit
Set CurItem = Nothing
Set CurFolder = Nothing
Set FSO = Nothing
ActiveWorkbook.Saved = True
End Function


Function ListFileAttr(a As Integer) As String

ListFileAttr = ""
If a And vbReadOnly Then
ListFileAttr = "R"
ElseIf a And vbHidden Then
ListFileAttr = ListFileAttr & "H"
ElseIf a And vbSystem Then
ListFileAttr = ListFileAttr & "S"
ElseIf a And vbVolume Then
ListFileAttr = ListFileAttr & "V"
ElseIf a And vbDirectory Then
ListFileAttr = ListFileAttr & "D"
ElseIf a And vbArchive Then
ListFileAttr = ListFileAttr & "A"
End If

End Function