Method to populate column w/ subfolder names within parent folder

[MHando]

I have a sheet in a parent folder that also contains hundreds of subfolders (these are each full of various files & other nested-subfolders). I want to populate a column in my sheet with the names of the subfolders in the parent folder only (this does not include any files or nested-subfolders).

Is this possible? And if so, what would be the smartest method to do this?

 

[PeteWright]

Hi @MHando and welcome to the Forum!

It is possible if you don't mind using VBA.

Paste the below code to your Workbook's VBA code within the VBA Editor (which you can activate via [Alt]+[F11] on Windows) like this:

Sub SubFolders()
   Dim WorkSheetName As String
   Dim StartCell As String
   'CHANGE AS DESIRED
   WorkSheetName = "Sheet1"
   StartCell = "A1"
  
   Dim FSO As Object: Set FSO = CreateObject("Scripting.FileSystemObject")
   Dim ParentFolder As Object: Set ParentFolder = FSO.getfolder(Application.ThisWorkbook.Path)
   Dim SubFolders As Object: Set SubFolders = ParentFolder.SubFolders
   Dim Folder As Variant
   Dim FolderName As String: FolderName = ""
   Dim r As Range: Set r = ThisWorkbook.Worksheets(WorkSheetName).Range(StartCell)
   Dim i As Long
   i = r.Row
   r.Activate
   For Each Folder In SubFolders
      Cells(i, r.Column).Value = FolderName & Folder.Name
      i = i + 1
   Next Folder
End Sub

Change the variables so they match your Worksheet's Name and the starting cell:
WorkSheetName = "Sheet1"
StartCell = "A1"

Then you can run the Macro (use [Alt]+[F8] on Windows) and see the output.

If you got any further questions just let me know

 

[MHando]

Hey @PeteWright - thank you! Good to be here so far

This looks like exactly what I was trying for, so really appreciate the pointers. Trying to replicate things though, I'm hitting a snag and am not sure why yet:

I've plugged in your VBA code and updated my strings to
WorkSheetName = "Project_Tracker"
StartCell = "A4"

but when trying to run the macro, I'm getting a run-time error back and it's flagging this line as path not found. Any idea what it's tripping over on my end?

 

[PeteWright]

Uh, I think I know why that's happening.

Is your Excel Workbook on OneDrive, SharePoint, etc?

For some reason Excel with VBA containing the File System Object doesn't work well when the Workbook is "online" (OneDrive and similar).

Try to copy your Workbook to a local folder (like C:\TEMP) and try it again.

If that works we'll see what we can do about that "online issue".

Please keep me updated.

 

[PeteWright]

Meanwhile I found a solution that seems to work:

Sub SubFolders()
   Dim OneDrivePath As String
   Dim ExcelFileLocation As String
   Dim WorkSheetName As String
   Dim StartCell As String

   'CHANGE AS DESIRED
   OneDrivePath = "C:\Users\John\OneDrive\"
   ExcelFileLocation = "Desktop\" 'relative path within the OneDrive Path
   WorkSheetName = "Sheet1"
   StartCell = "A1"
   
   Dim FSO As Object: Set FSO = CreateObject("Scripting.FileSystemObject")
   Dim ParentFolder As Object: Set ParentFolder = FSO.getfolder(OneDrivePath & ExcelFileLocation)
   Dim SubFolders As Object: Set SubFolders = ParentFolder.SubFolders
   Dim Folder As Variant
   Dim FolderName As String: FolderName = ""
   Dim r As Range: Set r = ThisWorkbook.Worksheets(WorkSheetName).Range(StartCell)
   Dim i As Long
   i = r.Row
   r.Activate
   For Each Folder In SubFolders
      Cells(i, r.Column).Value = FolderName & Folder.Name
      i = i + 1
   Next Folder
End Sub

 

[MHando]

This was it exactly - I had my parent folder in my OneDrive. I was just able to test these both out though and these scripts solved the issue in both my local test folder and the original I had in OneDrive.

You're a wizard - thank you! Being able to finally sync my folders like this is a huge improvement

 

[PeteWright]

@MHando glad I could help

If you need any further help just ask.

This was it exactly - I had my parent folder in my OneDrive. I was just able to test these both out though and these scripts solved the issue in both my local test folder and the original I had in OneDrive.

You're a wizard - thank you! Being able to finally sync my folders like this is a huge improvement

Just noticed you're writing about syncing folders. May I ask what you're planning to do?
If it's something about synchronization of files and folders there's this neat software I use called Free File Sync. freefilesync.org