List files in a folder with Microsoft Scripting Runtime

Microsoft Scripting Runtime is included in these products: Windows98, Windows2000, IE5, and Office2000. The macro examples below assumes that your VBA project has added a reference to the Microsoft Scripting Runtime library. You can do this from within the VBE by selecting the menu Tools, References and selecting Microsoft Scripting Runtime.

Sub TestListFilesInFolder()
    Workbooks.Add ' create a new workbook for the file list
    ' add headers
    With Range("A1")
        .Formula = "Folder contents:"
        .Font.Bold = True
        .Font.Size = 12
    End With
    Range("A3").Formula = "File Name:"
    Range("B3").Formula = "File Size:"
    Range("C3").Formula = "File Type:"
    Range("D3").Formula = "Date Created:"
    Range("E3").Formula = "Date Last Accessed:"
    Range("F3").Formula = "Date Last Modified:"
    Range("G3").Formula = "Attributes:"
    Range("H3").Formula = "Short File Name:"
    Range("A3:H3").Font.Bold = True
    ListFilesInFolder "C:\FolderName", True 
    ' list all files included subfolders
End Sub

Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName", True
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim r As Long
    Set FSO = New 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.Path & FileItem.Name
        Cells(r, 2).Formula = FileItem.Size
        Cells(r, 3).Formula = FileItem.Type
        Cells(r, 4).Formula = FileItem.DateCreated
        Cells(r, 5).Formula = FileItem.DateLastAccessed
        Cells(r, 6).Formula = FileItem.DateLastModified
        Cells(r, 7).Formula = FileItem.Attributes
        Cells(r, 8).Formula = FileItem.ShortPath & FileItem.ShortName
        ' use file methods (not proper in this example)
'        FileItem.Copy "C:\FolderName\Filename.txt", True
'        FileItem.Move "C:\FolderName\Filename.txt"
'        FileItem.Delete True
        r = r + 1 ' next row number
    Next FileItem
    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            ListFilesInFolder SubFolder.Path, True
        Next SubFolder
    End If
    Columns("A:H").AutoFit
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
    ActiveWorkbook.Saved = True
End Sub

Document last updated 2000-02-04 12:49:08 Printerfriendly version

User comments:

Ole P. from Norway wrote (2006-09-10 22:33:57 CET):

Re: i was looking for this for a lon long time
You can add hyperlink information like this:
Cells(r, 1).Formula = FileItem.Path & FileItem.Name
ActiveSheet.Hyperlinks.Add Anchor:=Cells(r, 1), _
Address:=Cells(r, 1).Formula, _
TextToDisplay:=Cells(r, 1).Formula

You will have to run the macro again to update the list with information for new files added to or removed from the folder since the macro was last run.

eray from istanbul/turkiye wrote (2006-09-08 14:20:16 CET):

i was looking for this for a lon long time
i guess this what i have been looking for. i need to make a very long list of files in a folder. actually i need the names and the hyperlinks of the files . i really dont know how to use it right now or it has an ability to hyperlink those files? can you please tell me how to make the hyperlinks for the folder? i do appreciate your work and it will sure be very useful for me. and just one more thing: does it update the list in itself when i add a new file in the same folder?

Ole P. from Norway wrote (2005-01-28 17:45:15 CET):

Re: List Folder Names
Yes, see this new example.

P Wright from Leeds, UK wrote (2005-01-28 15:21:41 CET):

List Folder Names
Is It possible to list folder names in a similar way?

Ole P. from Norway wrote (2004-11-05 15:28:02 CET):

Re: Can you retreive the file author?
Visit Microsofts website.
You will find a description on how to read and edit Office document properties (including the Author) using the dsofile.dll.

bman from michigan wrote (2004-11-05 15:12:29 CET):

Can you retreive the file author?
Can you retreive the file author?

Richard T. from Canada wrote (2004-08-13 01:11:39 CET):

Re: values for FileItem.Type?
Thanks a lot for the tip. Either full text name or file extension works well (e.g., "Adobe Acrobat Document" or "pdf").

Ole P. from Norway wrote (2004-08-12 09:13:32 CET):

Re: values for FileItem.Type?
In the example above you can replace FileItem.Type with FSO.GetExtensionName(FileItem.Name) to return the file extension (e.g. txt, xls and pdf). This will probably make it easier to filter the files using an IF statement regarding the file type/extension.

Richard T. from Canada wrote (2004-08-11 23:46:39 CET):

what values for FileItem.Type?
I want to selectively list files with certain extensions only, e.g. *.xls, *.pdf, etc. I presume I can do this with If ... Then statement if I know the different values for each type of the files.
Otherwise the code works great, it is very useful.
Thank you.

Ole P. from Norway wrote (2004-07-16 20:31:16 CET):

Re: What values for Attributes?
The possible values for the Attribute property is available in the Object Browser.
Press F2 in the VBE to open the Object Browser.
Select "Scripting" in the Project/Library dropdown.
Select "FileAttribute" in the Classes listbox to see the available values.
The attribute property can return a combination of multiple values, e.g. 33 for a read-only (1) file with the archive (32) attribute set (1 + 32 = 33)