Create list in excel of all folders within a directory

I am a bit confused on how to come up with a macro that lists the folders in a directory

Directory: Q:\Deburr issue\WT Review\Ship 0008

There are also some .zip files in this directory, but i just want to list the folders.

Thanks,

Brandon
LVL 6
KnutsonBMAsked:
Who is Participating?
 
Rory ArchibaldCommented:
Change that to:
Dim fso As Object

Open in new window

0
 
Rory ArchibaldCommented:
Try this:
Sub PlainFolderListing()
   Dim fso As FileSystemObject, fdrSelected, fdrSub, filTemp
   Dim strFilePath As String, rngOutput As Range
   
   ' change folder as required
   strFilePath = "Q:\Deburr issue\WT Review\Ship 0008"
   
   ' change list destination as required
   Set rngOutput = ActiveSheet.Range("A1")
   
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set fdrSelected = fso.GetFolder(strFilePath)
   For Each fdrSub In fdrSelected.SubFolders
      rngOutput.value = fdrSub.Path
      Set rngOutput = rngOutput.Offset(1)
   Next fdrSub
   Set fso = Nothing

End Sub

Open in new window

0
 
KnutsonBMAuthor Commented:
at Dim fso As FileSystemObject i get 'Compile Error: User-defined type not defined'


any thoughts

Brandon
0
 
KnutsonBMAuthor Commented:
what do i need to do to remove the path and leave just the name of the folder on the output?
0
 
KnutsonBMAuthor Commented:
nevermind i got it, changed fdrSub.Path to fdrSub.Name
0
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.