List files in a folder with Microsoft Scripting Runtime using VBA in Microsoft Excel

by  About
       

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


4 thoughts on “List files in a folder with Microsoft Scripting Runtime 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 *


× eight = 48

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>