Excel 2010 List all Files in a Folder and SubFolder

[Biz]

Dear Sir/Madam,

Application.FileSearch is not supported in Excel 2010.

Option Compare Text
 'The following is a function to call the directory browse window
Private Const BIF_RETURNONLYFSDIRS As Long = &H1
Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2
Private Const BIF_RETURNFSANCESTORS As Long = &H8
Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
Private Const BIF_BROWSEFORPRINTER As Long = &H2000
Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
Private Const MAX_PATH As Long = 260
 
Type BrowseInfo
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszINSTRUCTIONS As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
End Type
 
Type SHFILEOPSTRUCT
    hwnd As Long
    wFunc As Long
    pFrom As String
    pTo As String
    fFlags As Integer
    fAnyOperationsAborted As Boolean
    hNameMappings As Long
    lpszProgressTitle As String
End Type
 
Declare Function SHGetPathFromIDListA Lib "shell32.dll" ( _
ByVal pidl As Long, _
ByVal pszBuffer As String) As Long
Declare Function SHBrowseForFolderA Lib "shell32.dll" ( _
lpBrowseInfo As BrowseInfo) As Long
 
Function BrowseFolder(Optional Caption As String = "") As String
     
    Dim BrowseInfo As BrowseInfo
    Dim FolderName As String
    Dim ID As Long
    Dim Res As Long
     
    With BrowseInfo
        .hOwner = 0
        .pidlRoot = 0
        .pszDisplayName = String$(MAX_PATH, vbNullChar)
        .lpszINSTRUCTIONS = Caption
        .ulFlags = BIF_RETURNONLYFSDIRS
        .lpfn = 0
    End With
    FolderName = String$(MAX_PATH, vbNullChar)
    ID = SHBrowseForFolderA(BrowseInfo)
    If ID Then
        Res = SHGetPathFromIDListA(ID, FolderName)
        If Res Then
            BrowseFolder = Left$(FolderName, InStr(FolderName, _
            vbNullChar) - 1)
        End If
    End If
     
End Function
 
Sub listfilesinfoldersandsub()
    Dim i               As Long
    Dim Path            As String
    Dim Prompt          As String
    Dim Title           As String
    Dim TempArr() As String
    With Application.FileSearch
         
         'Presently using the BrowseFolder function
         'Comment the following code out to use a fixed path
         'or to look in the path where this workbook resides
        Path = BrowseFolder("Select A Folder")
        If Path = "" Then
            Exit Sub
        Else
            .LookIn = Path
             'If you comment out the preceding code, uncomment one of the
             'following two lines
             '    .LookIn = "F:\Temp\"
             '    .LookIn = ThisWorkbook.Path 'Change to root path
            .FileType = msoFileTypeAllFiles
            .SearchSubFolders = True
            .Execute
             'If you comment out the path code using the function
             'then you will need to comment the following End If also
        End If
        For i = 1 To .FoundFiles.Count
             
            TempArr = Split(.FoundFiles(i), Application.PathSeparator)
            Range("A" & i).Resize(1, UBound(TempArr) + 1) = TempArr
             'comment the two lines above and the Dim TempArr() As String at the beginning of sub
             'and uncomment the line below to use this without putting
             'each directory in a seperate cell
             '    Range("A" & i).Value = .FoundFiles(i)
             
        Next i
    End With
    Columns.AutoFit
End Sub

Could you help me make this code compliant with both Excel 2003 and Excel 2010?

Kind Regards,

Biz

 

[wigi]

Hello

This question is asked so many times a week / month.
For example, see Tushar Mehta's contribution here:
http://www.mrexcel.com/forum/showth...of-a-directory-using-macros&p=64850#post64850

It's just 1 of many code examples.

Wigi

 

[Kyle123]

FWIW I use

Sub test()
Const path As String = "C:\My Documents\"
Dim files
files = Split(CreateObject("wscript.shell").exec("cmd /c dir " & path & " /s /b").StdOut.ReadAll, vbCrLf)
Sheet3.Cells(1, 1).Resize(UBound(files), 1).Value = Application.Transpose(files)
End Sub

 

[wigi]

Hello Kyle

If you want to cut down on number of characters in your code, you could use:

Sub test()
    Path = "C:\My Documents\"
    Files = Split(CreateObject("wscript.shell").exec("cmd /c dir " & Path & " /s /b").StdOut.ReadAll, vbLf)
    Sheet3.Cells(1).Resize(UBound(Files)) = Application.Transpose(Files)
End Sub

 

[Andrew Poulsom]

Hello Kyle

If you want to cut down on number of characters in your code, you could use:

Sub test()
    Path = "C:\My Documents\"
    Files = Split(CreateObject("wscript.shell").exec("cmd /c dir " & Path & " /s /b").StdOut.ReadAll, vbLf)
    Sheet3.Cells(1).Resize(UBound(Files)) = Application.Transpose(Files)
End Sub

Not with Option Explicit, which in my opinion should always be used.

 

[wigi]

Not with Option Explicit, which in my opinion should always be used.

That's right. I respect your opinion.

 

[Kyle123]

I too am a believer in Option Explicit There are far more benefits of declaring your variables than not, in fact other than laziness are there any benefits of not declaring them?

 

[wigi]

I appreciate less code to read, write and maintain (i.e. read again at later times).
Variable names are often chosen in such a way that, when the business logic changes later on,
the variable name becomes inappropriate soon.

Kyle123:
On a different note, your code above does not contain error handling for a non-existing folder.
How do you approach this? Offer a browse for folder to the user, or separate error handling, or else?

 

[Kyle123]

I can agree that variable names may lose their meanings, but that is down to proper code maintenance and design, using variants in place of other types slows code down, so I tend to always declare types explicitly.

As for error handling I break my code down into small re-usable functions so I tend to do error handling dependant on the application on a higher level than the function. I prefer to write stupid re-usable code than trying to be clever and having to adapt, the function I actually use looks more like - that way it produces a meaningful error, but you could just as easily return a zero length string if the folder/file doesn't exist

Function ListFiles(Path As String) As Variant

Dim files
files = Split(CreateObject("wscript.shell").exec("cmd /c dir " & Path & " /s /b").StdOut.ReadAll, vbCrLf)


If UBound(files) > -1 Then
    ListFiles = files
Else
    'error handle
    Err.Raise 53
End If

 

[taurean]

I had also tried this code. The only limitation was you can't 'add' more attributes than DOS has to offer. That is where FileSystemObject and Shell object are more useful than this code.

I have added few more items to this code to get some basic formatting and data arrangement. See:
http://www.mrexcel.com/forum/showth...isting-(DIR)&p=3212601&viewfull=1#post3212601