File search and return size of files

[Shippwreck]

Hi,

I thought this would be simple, but i'm having issues getting it to work!

Basically i'm trying to create a macro in Excel where i give it a folder and a minimum file size (Mb, Kb, b, it doesn't matter) and it looks through the drive and lists in columns a and b all the file names and the file size of files greater than the specified size.

I have code (taken from the excel help files) that lists out all the files in a single folder and there size using the following:

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.Files
For Each f1 In fc
If f1.size / 1024 / 1024 > size Then
s = s & f1.Name & FormatNumber(f1.size / 1024 / 1024, 0)
s = s & vbCrLf
End If
Next
MsgBox s

yes i know its a message box, just trying to get it to work! but i can;t work out how to get it to look through subfolders of that folder, any ideas???

Any help is greatly appreciated, thanks!

http://www.tazforum.thetazzone.com

"Google is god...of the internet" - Me

 

[EBGreen]

Search the fora for these terms:

Recursion
Recurse
Recursive

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[fumei]

Something like?

Dim fld As Scripting.Folders
Dim fil as Scripting.File
Set fld As f.SubFolders
For Each fld In f.SubFolders
   For Each fil In fld.Files

Gerry
My paintings and sculpture

 

[Shippwreck]

ahhh,

thanks fumei, the code you have written gave me an idea which led me too... (in the helpfiles)

Sub ShowFolderListv2(folderspec)
Dim fs, f, f1, s, sf
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set sf = f.SubFolders
For Each f1 In sf
s = s & f1.Name
s = s & vbCrLf
Next
MsgBox s
End Sub

and all i have to do is keep looping through each folder... (i think)

http://www.tazforum.thetazzone.com

"Google is god...of the internet" - Me