VBA > Function > Count Folders

Code

' module name: mod_File_GetCountFolders
'*************** Code Start *****************************************************
' Purpose  : Count the number of subfolders in a folder given a path
' Author   : crystal (strive4peace) 
' Return   : Long
' License  : below code
' Code List: www.MsAccessGurus.com/code.htm
'--------------------------------------------------------------------------------
'                              GetCountFolders
'--------------------------------------------------------------------------------
Function GetCountFolders(psPath As String) As Long
'strive4peace 
'uses Late Binding. Reference for Early Binding:
'  Microsoft Scripting Runtime
   'PARAMETER
   '  psPath is path to get the number of folders for
   '     for example, c:\myPath
   ' Return: Long
   '  -1 = path not valid
   '   0 = no folders found, but path is valid
   '  99 = number of folders where 99 is some number
   
   'inialize return value 
   GetCountFolders = -1 
   'skip errors    
   On Error Resume Next
   'count SubFolders in FileSystemObject for psPath   
   With CreateObject("Scripting.FileSystemObject")
      GetCountFolders = .GetFolder(psPath).SubFolders.Count
   End With
End Function
'
' LICENSE
'   You may freely use and share this code 
'     provided this license notice and comment lines are not changed;
'     code may be modified provided you clearly note your changes.
'   You may not sell this code alone, or as part of a collection,
'     without my handwritten permission.
'   All ownership rights reserved. Use at your own risk.  
'   ~ crystal (strive4peace)  www.MsAccessGurus.com
'*************** Code End *******************************************************

'--------------------------------------------------------------------------------
'                              call_GetCountFolders_MsgBox
'--------------------------------------------------------------------------------
Sub call_GetCountFolders_MsgBox ()
   'click HERE and press F5 to run
   Dim sPath As String
   sPath = "c:\myPath"    '------------- customize 
   MsgBox Format(GetCountFolders(sPath), "#,##0") _
      & " folders in " & sPath _ 
      , , "GetCountFolders"
End Sub
'--------------------------------------------------------------------------------
'                              call_GetCountFolders_BadPath
'--------------------------------------------------------------------------------
Sub call_GetCountFolders_BadPath ()
   'click HERE and press F5 to run
   Dim sPath As String _
      , nCount As Long
      
   sPath = "c:\invalid"
   nCount = GetCountFolders(sPath)
   
   If nCount < 0 Then
      MsgBox sPath & " is not valid" _
         , , "GetCountFolders"
   Else
      MsgBox Format(nCount, "#,##0") _
         & " folders in " & sPath _
         , , "GetCountFolders"
   End If
End Sub

Logic

Assign the function return value to be -1 to indicate that the passed path isn't valid.

Skip all errors.

Use With to create a temporary instance of FileSystemObject so space for a variable that needs to be released isn't allocated.

Late binding is used so that the VBA project doesn't have to reference the Microsoft Scripting Runtime library.

Get the number of folders using the .SubFolders.Count property of GetFolder for the given path (psPath).

Because no storage is set aside for an object variable, there is not one to release.

This VBA can be used in applications other than Access ... Excel, Part, PowerPoint, Project, Visio, ...

Parameter

psPath

• psPath is the path to count folders in such as c:\myPath