so close...trying get number of subfolders in a folder

dnickelson

Board Regular
Joined
Oct 30, 2003
Messages
118
Been grabbing several snippets and full macro's off the board trying to modify, but have been unable to find anything that will give me number of subfolders listed under a main folder.

I've tried so many I don't even know which to post to see if someone can modify, but all the code I've been finding seems to focus on returning files, names, paths etc. (a bunch from tushar which I needed and used as well)

I know if I could see it from the right point of view, or if I just hacked at the recursive piece long enough, I could grab the unique folders out of the results and get what I need, but short of pasting in half dozen random, half modified subs in here, anyone have a simple solution or re-starting point for me?

Thanks much,
-Dan
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
Dan

Do you actually want the number of subfolders?

Or do you want to do something with each folder?
 

KenWright

Active Member
Joined
Jan 14, 2005
Messages
267
Googled and tweaked to let you select a starting folder - Dump this in a module and run the sub:-



Code:
Sub Countfolders()
Dim starthere As String
   starthere = PickFolder(strStartDir)
   If starthere = "" Then
      MsgBox "Canceled"
      Exit Sub
   End If
   MsgBox CountSubFolders(starthere)
End Sub


Function PickFolder(strStartDir As Variant) As String
  Dim SA As Object, F As Object
  Set SA = CreateObject("Shell.application")
  Set F = SA.BrowseForFolder(0, "Choose a folder", 0, strStartDir)
  If (Not F Is Nothing) Then
    PickFolder = F.items.Item.Path
  End If
  Set F = Nothing
  Set SA = Nothing
End Function


Function CountSubFolders(ByVal StartFolder As String) As Long
   Dim fso, F, f1, s, sf
   Dim lngCount As Long

   On Error GoTo ErrHandler

   Set fso = CreateObject("Scripting.FileSystemObject")

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

   Set F = fso.GetFolder(StartFolder)
   Set sf = F.SubFolders
   lngCount = sf.Count
   For Each f1 In sf
      'Debug.Print StartFolder, f1.Name
       lngCount = lngCount + CountSubFolders(StartFolder & f1.Name)
   Next

ExitFunc:
   Set sf = Nothing
   Set F = Nothing
   Set fso = Nothing
   CountSubFolders = lngCount
   Exit Function

ErrHandler:
   'Do some error handling here
   Resume ExitFunc
End Function
 

dnickelson

Board Regular
Joined
Oct 30, 2003
Messages
118
Slick, this forum is the best. Just looking for the number of folders, already have all the coding i needed for the stuff inside. Ken's code shot out the number like there was nothing to it.

Have to sit and figure it out how it does it when I get a chance, but for now all i need to do is set it to run though the list of folders I'm keeping track of and I'm done. I forgot to mention that I was working with folders on a mapped network drive, which I'd had problems with with some of the other code, but it seems to work fine there as well.

Thanks a bunch guys.
-Dan
 

Forum statistics

Threads
1,147,846
Messages
5,743,521
Members
423,801
Latest member
paulj4177

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top