List of Folders

Good morning all,

I have an excel file that I am using to create an index. What I want to do is have excel list all of the folder names that are in the same directory as the excel file. I can get the full path name listed but not just the folder name. Any ideas?

Thanks in advance,
tc

Hello toocold,

Here is a macro that will list the directory your are searching in cell "A1" of the Active Sheet, and all the folder names in it starting at cell "A2".

Sub ListFolders(Optional FolderPath As String)

  Dim FolderName As String
  Dim R As Long
  
    R = 2
    If FolderPath = "" Then FolderPath = CurDir
    If Right(FolderPath, 1) <> "\" Then FolderPath = FolderPath & "\"
    
      ActiveSheet.Cells(1, "A") = FolderPath
      FolderName = Dir(FolderPath, vbDirectory)    ' Retrieve the first entry.
      
        Do While FolderName <> ""    ' Start the loop.
         'Ignore the current directory and the encompassing directory.
          If FolderName <> "." And FolderName <> ".." Then
           'Use bitwise comparison to make sure MyName is a directory.
            If (GetAttr(FolderPath & FolderName) And vbDirectory) = vbDirectory Then
              ActiveSheet.Cells(R, "A") = FolderName
              R = R + 1
            End If
          End If
          FolderName = Dir
        Loop

End Sub

Sincerely,
Leith Ross

thanks guys, works great

Hi

This will use the currently selected file, and put the output in the file with the macro. That way you don't have to put the macro into the file you are testing....

Sub aaa()
  Set fs = CreateObject("scripting.filesystemobject")
  Set outfile = ThisWorkbook.Sheets("sheet1")
  outfile.Range("A:A").ClearContents
  outfile.Range("A1").Value = ActiveWorkbook.Path
  For Each f In fs.getfolder(ActiveWorkbook.Path).subfolders
    outfile.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = f
  Next f
  
End Sub

rylo