List Directories and Subdirectories

S

Steven

Is there macro make a list in excel of the directories and subdirectories
under a certain directory: For example:

C:\

and then list all the directories and subdirectories in an excel file.

But not the files.

Thank you,

Steven
 
J

Joel

I pulled this code from my archieves. It does exactly what you want and also
gets the sizes (bytes) of each folder.


Dim RowNumber
Sub GetFolderSize()

strFolder = "C:"
RowNumber = 1

Set fso = CreateObject _
("Scripting.FileSystemObject")
Set folder = _
fso.GetFolder(strFolder)

Sheets(1).Cells(RowNumber, 1) = strFolder + "\"
Sheets(1).Cells(RowNumber, 2) = folder.Size
RowNumber = RowNumber + RowNumber

Call GetSubFolderSize(strFolder + "\")
End Sub

Sub GetSubFolderSize(strFolder)
Set fso = CreateObject _
("Scripting.FileSystemObject")

Set folder = _
fso.GetFolder(strFolder)

If folder.subfolders.Count > 0 Then
For Each sf In folder.subfolders
On Error GoTo 100
Call GetSubFolderSize(strFolder + sf.Name + "\")
100 Next sf
End If
'folder size in bytes
On Error GoTo 200
If Not folder.isrootfolder Then
FolderSize = folder.Size
Sheets(1).Cells(RowNumber, 2) = FolderSize
Sheets(1).Cells(RowNumber, 1) = strFolder
RowNumber = RowNumber + 1
End If

200 On Error GoTo 0

End Sub
 
S

Steven

That is incredible. Thank you.

Joel said:
I pulled this code from my archieves. It does exactly what you want and also
gets the sizes (bytes) of each folder.


Dim RowNumber
Sub GetFolderSize()

strFolder = "C:"
RowNumber = 1

Set fso = CreateObject _
("Scripting.FileSystemObject")
Set folder = _
fso.GetFolder(strFolder)

Sheets(1).Cells(RowNumber, 1) = strFolder + "\"
Sheets(1).Cells(RowNumber, 2) = folder.Size
RowNumber = RowNumber + RowNumber

Call GetSubFolderSize(strFolder + "\")
End Sub

Sub GetSubFolderSize(strFolder)
Set fso = CreateObject _
("Scripting.FileSystemObject")

Set folder = _
fso.GetFolder(strFolder)

If folder.subfolders.Count > 0 Then
For Each sf In folder.subfolders
On Error GoTo 100
Call GetSubFolderSize(strFolder + sf.Name + "\")
100 Next sf
End If
'folder size in bytes
On Error GoTo 200
If Not folder.isrootfolder Then
FolderSize = folder.Size
Sheets(1).Cells(RowNumber, 2) = FolderSize
Sheets(1).Cells(RowNumber, 1) = strFolder
RowNumber = RowNumber + 1
End If

200 On Error GoTo 0

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top