Creating a list of Subfolders and sizes
 

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

Creating a list of Subfolders and sizes
 

See ...

http://www.exceltip.com/st/List_file...Excel/446.html


*** Please do rate ***




"Laurence Lombard" wrote:

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

Creating a list of Subfolders and sizes
 

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

"Laurence Lombard" wrote in message
...
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

Creating a list of Subfolders and sizes
 

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

Laurence Lombard wrote:
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

Creating a list of Subfolders and sizes
 

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

--
Regards,
Tom Ogilvy


"Naveen" wrote:

See ...

http://www.exceltip.com/st/List_file...Excel/446.html


*** Please do rate ***




"Laurence Lombard" wrote:

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

Creating a list of Subfolders and sizes
 

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.

--
Regards,
Tom Ogilvy


"Laurence Lombard" wrote:

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

Creating a list of Subfolders and sizes
 

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


"Laurence Lombard" wrote in message ...
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

Creating a list of Subfolders and sizes
 

Thanks for all the responses - I'll having to work through them
Laurence
"Jim Cone" wrote in message
...
You must be using a Windows version released after Windows 95.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Laurence Lombard" wrote in message
...
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

Creating a list of Subfolders and sizes
 

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



"Laurence Lombard" wrote in message
...
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

Creating a list of Subfolders and sizes
 

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

"Laurence Lombard" wrote in message
...
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



"Laurence Lombard" wrote in message
...
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