asked on

file listing in Excel - VBA Script

Hi,
I need a excel sheet it should make a list of files in one column and the path of the folders in another column, i tried it but i got succeeded to retrieve the files list in the given path, but i need the list of all files(in sub-folders and sub-sub-folders) in the given path. Please help me in doing it.
Thanks in advance.

This is the code I used ------



Private Sub CommandButton1_Click()
Dim sFile As String
    Dim i As Long
    i = 1
    sFile = Dir("F:\DotNetApps2009\*.*") '<=== change to suit
    Do While sFile <> ""
        Cells(i, "A").Value = sFile
        On Error Resume Next
        sFile = Dir
        On Error GoTo 0
        i = i + 1
    Loop
End Sub

Open in new window

ExcelGuide

Well, the following code works for me. Do not forget to add "Microsoft Script Control" in your reference.

Also change foldername.

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

Open in new window

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Patrick Matthews

CPSRI,

Forgot to mention, the code I just posted uses late binding, so there is no need to include a reference to the
Microsoft Scripting Runtime library.

Patrick

CPSRI

ASKER

thanks for your reply Mr. Psychotec, could you please help me in adding reference "Microsoft Script Control", how can i add the reference please?

Patrick Matthews

CPSRI,

To add a reference, in the VB Editor select Tools|References from the menu.

Did you try the code I posted? It requires no reference, and also does not require you to hard-code the folder path...

Patrick

CPSRI

ASKER

wow, Patrick, amazing, this is what exactly I want, and its very perfect thanks a lot,...thank you so much. I just tried your code, before that I tried with Mr. Psychotec's code and i stucked at adding reference. and then I tried your code its really perfect for me. Thank you.

CPSRI

ASKER

Its really perfect for my requirement.