Creating a list of Subfolders and sizes

  • Thread starter Thread starter Laurence Lombard
  • Start date Start date
L

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
 
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
 
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
 
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.
 
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
 
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
 
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
 
Back
Top