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

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions

CATEGORY - Files, Workbook, and Worksheets in VBA VERSION - All Microsoft Excel Versions

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

Book Store:

VIEW ALL BOOKS

Recommended Books:

Email Tip

Print Tip

Add to My Tips

Rate This Tip



1	2	3	4	5

Rating: 3.87 Views: 258928

ALTERNATIVE

Gary Posted on: 31-12-1969

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 ?

Can this be simplified?

Vikram Posted on: 31-12-1969

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?

RE: ALTERNATIVE

Ole P. Erlandsen Posted on: 31-12-1969

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

Urgent! VBA Help needed!

Vikram Posted on: 31-12-1969

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