List of folders to Excel using FSO

Hi,

I need to read a directory (folder/subfolder structure) and copy to an excel spreadsheet the name of the root folder (parent folder), the name of each folder, the number of items in each folder and the Size of the folder (meaning the sum of the physical size of all items it contains).
Lau_ForenAsked:
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.

LET (Learn Excel in Tamil)Reporting Automation ExpertCommented:
Hi

Here is the coding to list all the files in the particular path


Sub SelectAllfiles()

Dim Objfso As Object
Dim Folder As Object
Dim File As Object

Set Objfso = CreateObject("Scripting.FileSystemObject")

Set Folder = Objfso.GetFolder("C:\Users\Test")     'Change folder path
Range("A1").Select
For Each File In Folder.Files
ActiveCell.Offset(100000, 0).End(xlUp).Offset(1, 0).Value = File
Next

Set Objfso = Nothing
Set Folder = Nothing
Set File = Nothing

End Sub

Open in new window

0

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
Lau_ForenAuthor Commented:
Thank you!
Please, be aware that I am not interested in files, but only folders.
What about the number of items and the size of each folder (i meant, the space occupied for the files therein). ?
0
LET (Learn Excel in Tamil)Reporting Automation ExpertCommented:
Hi Lun,

Pls check this code

Sub SelectAllfiles()

Dim Objfso As Object
Dim Folder As Object
Dim File As Object

Set Objfso = CreateObject("Scripting.FileSystemObject")

Set Folder = Objfso.GetFolder("C:\Users\Test")     'Change folder path
Range("A1").Select
For Each File In Folder.Files
ActiveCell.Offset(100000, 0).End(xlUp).Offset(1, 0).Value = File
ActiveCell.Offset(100000, 0).End(xlUp).Offset(1, 1).Value = FileLen(File)
Next

Set Objfso = Nothing
Set Folder = Nothing
Set File = Nothing

End Sub

Open in new window

0
LET (Learn Excel in Tamil)Reporting Automation ExpertCommented:
Na
0
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
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.