Get name and size for directory and all subdirectories

I need to get directory name and size for a directory and all of its subdirectories (some are 2, 3 and 4 levels down).  The code I'm currently using is below, but this will only go down one level.

Dim fso As Scripting.FileSystemObject
Dim fldr
Dim subFldr
Dim lngSize As Double
Dim s
Dim strPathName As String
Dim strPath As String
Dim dbCurrent As DAO.Database
Dim qryAddPathInfo As DAO.QueryDef

If (Dir$(strFolderPath, vbDirectory) = "") Then Exit Sub
Set fso = New Scripting.FileSystemObject
Set fldr = fso.GetFolder(strFolderPath)
Set subFldr = fldr.SubFolders

Set dbCurrent = CurrentDb
Set qryAddPathInfo = CurrentDb.QueryDefs("qryAddDBPathSize")

For Each s In subFldr
    lngSize = s.Size
    'Convert folder size to gigabytes (from bytes)
    lngSize = (((lngSize / 1024) / 1024) / 1024)
    strPath = s.Path
   
    With qryAddPathInfo
        .Parameters(Path) = strPath
        .Parameters("Size") = lngSize
        .Execute
    End With
Next

Set dbCurrent = Nothing
Set qryAddPathInfo = Nothing
msanzenbAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TimCotteeHead of Software ServicesCommented:
Hi msanzenb,

You need to create a recursive function for this.

Private Sub GetFolderSizes()
Dim fso As Scripting.FileSystemObject
Dim fldr
Dim dbCurrent As DAO.Database
Dim qryAddPathInfo As DAO.QueryDef

If (Dir$(strFolderPath, vbDirectory) = "") Then Exit Sub
Set fso = New Scripting.FileSystemObject
Set fldr = fso.GetFolder(strFolderPath)

Set dbCurrent = CurrentDb
Set qryAddPathInfo = CurrentDb.QueryDefs("qryAddDBPathSize")
GetFldrSize(qryAddPathInfo,fldr)
End Sub

Private Sub GetFldrSize(ByRef QPathInfo As DAO.QueryDef, ByVal ThisFolder As Folder)
Dim lngSize As Double
Dim strPathName As String
Dim strPath As String
Dim s As SubFolder
For Each s In ThisFolder.SubFolders
    lngSize = s.Size
    'Convert folder size to gigabytes (from bytes)
    lngSize = (((lngSize / 1024) / 1024) / 1024)
    strPath = s.Path
    With QPathInfo
        .Parameters(Path) = strPath
        .Parameters("Size") = lngSize
        .Execute
    End With
    GetFldrSize(QPathInfo,s)
Next

Set dbCurrent = Nothing
Set qryAddPathInfo = Nothing
End Sub

This will iterate through each sub folder all the way down the tree and store the name/size for each folder.

Tim Cottee

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
msanzenbAuthor Commented:
Thanks - that worked perfect!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.