Creating a list of Subfolders and sizes

[Laurence Lombard]

I would like to be able to list the subfolders in any specified folder with
their respective sizes. In a search for a solution I copied this section
from the VBA's help, but this is not a standalone bit of code (why do they
not use standalone code for their examples?)

Sub ShowFolderList(folderspec)
Dim fs, f, f1, fc, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.SubFolders
For Each f1 In fc
s = s & f1.Name & " " & f1.Size 'I added the "Size" bit
s = s & vbCrLf
Next
MsgBox s

So I wrote this bit to call the above code

Sub test3()
folderspec = "C:\"
ShowFolderList (folderspec)
End Sub

However the line containing "Scripting.FileSystemObject" creates an error
"Runtime error 429 - ActiveX component can't create object"

What should folderspec be? How do I get it to work?

My VBA is self taught and I come unstuck when the help talks about
SearchFolders and ScopeFolders!

Thanks
Laurence

 

[Guest]

See ...

http://www.exceltip.com/st/List_fil...Runtime_using_VBA_in_Microsoft_Excel/446.html


*** Please do rate ***

 

[Jean-Yves]

Hi,

Check In VBA/Tools/References if you can find "Microsoft Scripting Runtime"
reference. The dll is scrrun.dll, which should be located in
C;\Windows\system32\
Regards
JY

 

[Andrew Taylor]

Your code works fine for me (though it takes a long time to
get the Size of folders that have a lot of subfolders), and
your folderspec of "C:\" is correct. The problem is in
creating Scripting.FileSystemObject. As far as I know
this should always be available, but it may be that
the object is not registered for some reason: if so
you could fix this with the command (from a command
window):

regsvr32 C:\windows\system32\scrrun.dll


Andrew

 

[Guest]

Doesn't that link just use the exact same technology the OP posted.

 

[Guest]

Sub ShowFolderList(folderspec)
Dim fs, f, f1, fc, s, sSize
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.SubFolders
For Each f1 In fc

On Error Resume Next
sSize = Format(f1.Size, "#,##0")
If Err.Number <> 0 Then
sSize = "NA"
End If
On Error GoTo 0
s = s & f1.Name & " " & sSize 'I added the "Size" bit
s = s & vbCrLf
Next
MsgBox s
End Sub

Sub test3()
folderspec = "C:\"
ShowFolderList (folderspec)
End Sub

worked fine for me.

the only problem with the original is that some folders choked on the size
command.

Also, a msgbox can only show 255 characters, so you string will be built,
but probably not entirely shown if you have a lot of subfolders.

 

[Jim Cone]

You must be using a Windows version released after Windows 95.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


I would like to be able to list the subfolders in any specified folder with
their respective sizes. In a search for a solution I copied this section
from the VBA's help, but this is not a standalone bit of code (why do they
not use standalone code for their examples?)

Sub ShowFolderList(folderspec)
Dim fs, f, f1, fc, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.SubFolders
For Each f1 In fc
s = s & f1.Name & " " & f1.Size 'I added the "Size" bit
s = s & vbCrLf
Next
MsgBox s

So I wrote this bit to call the above code

Sub test3()
folderspec = "C:\"
ShowFolderList (folderspec)
End Sub

However the line containing "Scripting.FileSystemObject" creates an error
"Runtime error 429 - ActiveX component can't create object"

What should folderspec be? How do I get it to work?

My VBA is self taught and I come unstuck when the help talks about
SearchFolders and ScopeFolders!

Thanks
Laurence

 

[Laurence Lombard]

Thanks for all the responses - I'll having to work through them
Laurence

 

[Laurence Lombard]

Andrew Taylor's Post provided the solution. Where did you get that
information?!

He suggested running the following from the command window

regsvr32 C:\windows\system32\scrrun.dll

I have cc'd this post to Andrew and Tom as I think they will be interested,
but new posts to this NG come in so fast they might not notice a follow-up
to an older post.

Thanks
Laurence

 

[Tom Ogilvy]

The scripting runtime has been around a long time and if you are using a
version of excel that shows that in the help, then it would expected to be
installed with that version of Office as a minimum. Possibly you have
mucked up your system in some way. I didn't actually scroll down and see
the bottom of your original message, so I answered with respect to an error
with the code rather than the environment.

Regards,
Tom Ogilvy