Friday, April 12, 2013

List files in a folder and sub-folders

Most of the people working in corporate environment deal with large amount data and are aware of the term 'Automation'. In the following discussion am sharing some automation tips and techniques which can be used in day to day work.

People working  on VBA coding in Excel will find this very useful and easy to understand. Below code can be used to list files in a folder and sub-folders.

Dim iRow

Sub ListFiles()
    iRow = 11
    Call ListMyFiles(Range("C7"), Range("C8"))
End Sub

Sub ListMyFiles(mySourcePath, IncludeSubfolders)
    Set MyObject = New Scripting.FileSystemObject
    Set mySource = MyObject.GetFolder(mySourcePath)
    On Error Resume Next
    For Each myFile In mySource.Files
        iCol = 2
        Cells(iRow, iCol).Value = myFile.Path
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Name
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Size
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.DateLastModified
        iRow = iRow + 1
    If IncludeSubfolders Then
        For Each mySubFolder In mySource.SubFolders
            Call ListMyFiles(mySubFolder.Path, True)
    End If
End Sub


  1. Hi,
    I've just tried the above code in Excel 2010 & get a compile error on line 2 - SetMyObject.
    it says "User-defined type not defined"
    What have I got missing ?

  2. Hi Steve, You need to select "Microsoft Scripting Runtime" from Tools > References.

    1. Thanks Arpan
      Thanks for sharing all this code.

  3. My pleasure Steve... Do check out other posts too, might be of help. Do subscribe via email.