listing files works, but how do I list files within folders (VBA, excel)?

Question

I have some code which I found online and it's working great. It lists any files in a specified folder and their properties. I just need to amend this function which returns a list of files. I need it to look in a given path at ALL files. So if it's a folder, open it and return those files too. I eventually need to get this to mark each file (so in a column in excel) where it came from. If it would be possible to create a hirarchy somehow that would be great.... but for now, just opening a folder if it finds on and listing those files would be awesome!

Any help is REALLY appreciated!

Private Function fcnGetFileList(ByVal strPath As String, Optional strFilter As String) As Variant
'   Returns a one dimensional array with filenames
'   Otherwise returns False

Dim f As String
Dim i As Integer
Dim x As Integer
Dim FileList() As String

    If strFilter = "" Then strFilter = "*.*" 'can provide an extension to check?

    'set the path
    Select Case Right$(strPath, 1)
     Case "\", "/"
         strPath = Left$(strPath, Len(strPath) - 1)
    End Select

    ReDim Preserve FileList(0)

    f = Dir$(strPath & "\" & strFilter)

    Do While Len(f) > 0
        ReDim Preserve FileList(i) As String
        FileList(i) = f
        i = i + 1
        f = Dir$()
    Loop

    If FileList(0) <> Empty Then
        fcnGetFileList = FileList
    Else
        fcnGetFileList = False
    End If

End Function

Do you really use a "fcn" prefix for all your functions? That's taking Hungarian notation way too far! :-) [And yet you have a string variable called 'f'...] — Gary McGill
– Gary McGill, Commented Jan 19, 2010 at 11:35
@Remou - the code will be run daily / weekly to run a series of reports to find out document properties etc. @Gary - haha, this isn't my code - it's part of some I downloaded! — Matt Facer
– Matt Facer, Commented Jan 20, 2010 at 11:07

gbianchi · Accepted Answer · 2010-01-18 15:38:25Z

1

Ideas:

Add the microsoft scripting runtime And use the file system object (much better file/folder handling)

Make this function recursive, add a vbdirectory parameter to the dir function, and use GetAtrr to get info about each file. If the file is a folder again, call this function again with this new folder.

answered Jan 18, 2010 at 15:38

gbianchi

2,14827 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matt Facer Over a year ago

I'm doing what you suggested and trying to check if it's a folder - then looping as suggested. Thanks :)

Collectives™ on Stack Overflow

listing files works, but how do I list files within folders (VBA, excel)?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related