Directory Handling with VBA

Reading a directory

Below code will read a directory in C:\ and will list all directory in Sheet1.

Private Sub cmdRun_Click()

Dim result() As String

Dim Program() As String

Dim dirname As String, count As Long

    Const ALLOC_CHUNK = 50

    ReDim result(ALLOC_CHUNK) As String

    ReDim Program(ALLOC_CHUNK) As String

    sDir = "C:\"

    If Right$(sDir, 1) <> "\" Then sDir = sDir & "\"

    dirname = Dir$(sDir & "*.*", vbDirectory Or Attributes)

    Do While Len(dirname)

        If dirname = "." Or dirname = ".." Then         ' Exclude the "." and ".." entries.

        ElseIf (GetAttr(sDir & dirname) And vbDirectory) = 0 Then             ' a regular file.

        Else

            '### This is directory. ###

            count = count + 1

            If count > UBound(result) Then                  ' Resize the result array

                ReDim Preserve result(count + ALLOC_CHUNK) As String

                ReDim Preserve Program(count + ALLOC_CHUNK) As String

            End If

            result(count) = dirname

            Range("A" & count) = result(count)     ' Write to Sheet1

         End If

        dirname = Dir$

        Loop

    ' Trim the result array.

    ReDim Preserve result(count) As String

End

End Sub