Excel VBA list the folders in a directory in an array to perform a task with each one

I am trying to run some code on a change event, when someone changes a drop down, I need this sub to go off, list all the folders in a specific directory by name only in an array. So I can perform a task then on each item in the array.

Is there a function someone has already written to do this? Or an efficient way of doing this?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Dim bTest As Boolean
Dim wb As Workbook
Dim MyArray as variant

If Target.Column = "3" Then
    bTest = Target.Row Mod 2 = 0
    If bTest = True And Target.Row > 3 And Target.Row < 39 Then
' Here I need to get array containing all folder names in a directory. (Don't need their subfolders)
    'MyArray = Function To Get Folder Names Back Into The Array

    Else: End If
Else: End If

 End Sub

Open in new window

Who is Participating?
SimonConnect With a Mentor Commented:
You can do this with the FileSystemObject. This example gets all the subfolders of the chosen basefolder - in this case "C:\" - and adds them to array, then lists the array elements.
Sub test()
Dim arrFolders() As String
Dim ctr As Integer

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("C:\")
Set colSubfolders = objFolder.Subfolders
For Each objsubfolder In colSubfolders
    ctr = ctr + 1
    ReDim Preserve arrFolders(ctr)
    arrFolders(ctr) = objsubfolder.Name

'print each item from the array
For ctr = LBound(arrFolders) To UBound(arrFolders)
    Debug.Print ctr, arrFolders(ctr)
End Sub

Open in new window

James ElliottManaging DirectorCommented:
Here's a great tutorial of the DIR function.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.