D
Damon
You may want to go straight to ***MY AIM*** below or you can read the history..
Back in May in a thread titled 'System calls in Excel' I asked If it is
possible to get the properties of a folder/drive from within an Excel macro
and then populate separate cells with the folder/drive size, number of files
& number of folders?
To which Ardus Petus & papuo both very kindly responded, with macro code.
The methods posted were quite different from each other and produce quite
different output. (for those that don't know you can get to the thread by
clicking my display name in blue at top of post and the clicking on 'Recent
posts by this user').
papuo's code will list the starting folder's path, size and the number of
files and number of Subfolders. The 'size' would be accurate (bare in mind
that the result is /1024 in the example so shows KB rather than Bytes) to
what you would get if you got the properties of the folder from the OS right
click menu. However it would only count the files and subfolders within that
folder and not the total of each contained within that folder AND it's
subfolders. It will then do the same for each of the subfolders (recurring).
Ardus's code worked fine for me at home (with Office 2003 on XP) apart from
the size was not quite correct as Ardus had already said in his post, however
at work (which is where I need the code) we have Office 2000 on NT4 machines
and the code fell over at 'BrowseFolder'. I have investigated this and
believe it to be due to the fact that as far as VBE is concerned 'Microsoft
Shell Controls and Automation' is 'Missing' (I have looked and Shell32 does
exist in the correct folder, but I do not think I can fix this). Although I
did manage to 'bodge' Ardus's code to work on a pre-populated list of folder
paths.
So now both sets code will run on my machine at work but Very slowly, and
they would only get so far before falling over on a buffer over run. At which
point with, Ardus’s code I had bodged, I could restart on the next folder on
the list and it would get the next few folders before stopping once more.
All in all it takes most of the afternoon or longer to get the information
from the root folders of one network drive, longer than it would take to get
the information by getting the properties for each folder by hand. I tried
the usual tactics of turning off screen update and automatic calculations but
these did little to help the speed.
I’ve started creating a macro from scratch for this but let me clarify..
***MY AIM***
I am trying to write a macro that will list the root folders in a network
drive and then provide the size, the total number of subfolders and total
number of files contained within each root folder – the same information as
right clicking on a folder and selecting ‘Properties’.
With the help of http://www.techbookreport.com/tutorials/fso2.html
(tweaking sample code to allow for the fact that 'Microsoft Shell Controls
and Automation' is 'Missing') and by referring to Ardus’s and papuo’s codes I
came up with a one sub macro that lists all the root folders and there
accurate size but like papuo’s code will only list that folders file and
subfolder count not the totals. (but it is still quite fast at this point,
even before speed tricks)
Looking again at the macros provided I came to the conclusion I probably
need to use a self-calling Sub or Function to be able to get the total file
and subfolder counts so I have tried to code one and this is where I am now
with a syntax error which at this moment I cannot get my head round. My code
is copied below.
I am aware that this is the point at which my macro is likely to get quite a
bit slower and maybe suffer the same crashing as the macros provided in the
first post but I would still like to understand my error.
I found DoEvents whilst researching before making this post, which I shall
experiment with re the crashing and I found the same or similar code in two
threads regarding using Arrays to query folders for information quikly, but I
shall have to look at that when I have some time and try to understand it
better.
In the meantime any help will be greatly appreciated.
I know there are dir type Addins and such available but they don’t quite do
what I’m after and invariably there code is locked preventing me learning
from it. However they do do what they do very fast and without crashing so I
know what I’m after must be possible.
Damon
Dim fldCount As Integer
Dim filCount As Integer
Dim fso As Object
Sub testfdrive()
Dim rtflds As Object
Dim strText As String
Dim i As Integer
Set fso = CreateObject("Scripting.FileSystemObject")
Set rtflds = fso.GetFolder("f:\").subfolders
i = 2
For Each f In rtflds
strText = f.Path
Worksheets("f").Cells(i, 1) = strText
strText = f.Size
Worksheets("f").Cells(i, 2) = strText
fldCount = f.subfolders.Count
filCount = f.Files.Count
If f.subfolders.Count > 0 Then
FaFCount f.subfolders, fldCount, filCount, i
End If
Worksheets("f").Cells(i, 3) = fldCount
Worksheets("f").Cells(i, 4) = filCount
i = i + 1
Next
Range("a1").Activate
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
Sub FaFCount(Fldr As Object, fldCount As Integer, filCount As Integer, indx
As Integer)
Dim sflds As Object
Dim sbfd As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set sflds = fso.GetFolder Fldr.subfolders '<----SYNTAX ERROR!!!!!!!!!
For Each sbfd In sflds
fldCount = fldCount + sbfd.subfolders.Count
filCount = filCount + sbfd.Files.Count
Next sbfd
If sbfd.subfolders.Count > 0 Then
FaFCount sbfd.subfolders, fldCount, filCount, indx
End If
End Sub
Back in May in a thread titled 'System calls in Excel' I asked If it is
possible to get the properties of a folder/drive from within an Excel macro
and then populate separate cells with the folder/drive size, number of files
& number of folders?
To which Ardus Petus & papuo both very kindly responded, with macro code.
The methods posted were quite different from each other and produce quite
different output. (for those that don't know you can get to the thread by
clicking my display name in blue at top of post and the clicking on 'Recent
posts by this user').
papuo's code will list the starting folder's path, size and the number of
files and number of Subfolders. The 'size' would be accurate (bare in mind
that the result is /1024 in the example so shows KB rather than Bytes) to
what you would get if you got the properties of the folder from the OS right
click menu. However it would only count the files and subfolders within that
folder and not the total of each contained within that folder AND it's
subfolders. It will then do the same for each of the subfolders (recurring).
Ardus's code worked fine for me at home (with Office 2003 on XP) apart from
the size was not quite correct as Ardus had already said in his post, however
at work (which is where I need the code) we have Office 2000 on NT4 machines
and the code fell over at 'BrowseFolder'. I have investigated this and
believe it to be due to the fact that as far as VBE is concerned 'Microsoft
Shell Controls and Automation' is 'Missing' (I have looked and Shell32 does
exist in the correct folder, but I do not think I can fix this). Although I
did manage to 'bodge' Ardus's code to work on a pre-populated list of folder
paths.
So now both sets code will run on my machine at work but Very slowly, and
they would only get so far before falling over on a buffer over run. At which
point with, Ardus’s code I had bodged, I could restart on the next folder on
the list and it would get the next few folders before stopping once more.
All in all it takes most of the afternoon or longer to get the information
from the root folders of one network drive, longer than it would take to get
the information by getting the properties for each folder by hand. I tried
the usual tactics of turning off screen update and automatic calculations but
these did little to help the speed.
I’ve started creating a macro from scratch for this but let me clarify..
***MY AIM***
I am trying to write a macro that will list the root folders in a network
drive and then provide the size, the total number of subfolders and total
number of files contained within each root folder – the same information as
right clicking on a folder and selecting ‘Properties’.
With the help of http://www.techbookreport.com/tutorials/fso2.html
(tweaking sample code to allow for the fact that 'Microsoft Shell Controls
and Automation' is 'Missing') and by referring to Ardus’s and papuo’s codes I
came up with a one sub macro that lists all the root folders and there
accurate size but like papuo’s code will only list that folders file and
subfolder count not the totals. (but it is still quite fast at this point,
even before speed tricks)
Looking again at the macros provided I came to the conclusion I probably
need to use a self-calling Sub or Function to be able to get the total file
and subfolder counts so I have tried to code one and this is where I am now
with a syntax error which at this moment I cannot get my head round. My code
is copied below.
I am aware that this is the point at which my macro is likely to get quite a
bit slower and maybe suffer the same crashing as the macros provided in the
first post but I would still like to understand my error.
I found DoEvents whilst researching before making this post, which I shall
experiment with re the crashing and I found the same or similar code in two
threads regarding using Arrays to query folders for information quikly, but I
shall have to look at that when I have some time and try to understand it
better.
In the meantime any help will be greatly appreciated.
I know there are dir type Addins and such available but they don’t quite do
what I’m after and invariably there code is locked preventing me learning
from it. However they do do what they do very fast and without crashing so I
know what I’m after must be possible.
Damon
Dim fldCount As Integer
Dim filCount As Integer
Dim fso As Object
Sub testfdrive()
Dim rtflds As Object
Dim strText As String
Dim i As Integer
Set fso = CreateObject("Scripting.FileSystemObject")
Set rtflds = fso.GetFolder("f:\").subfolders
i = 2
For Each f In rtflds
strText = f.Path
Worksheets("f").Cells(i, 1) = strText
strText = f.Size
Worksheets("f").Cells(i, 2) = strText
fldCount = f.subfolders.Count
filCount = f.Files.Count
If f.subfolders.Count > 0 Then
FaFCount f.subfolders, fldCount, filCount, i
End If
Worksheets("f").Cells(i, 3) = fldCount
Worksheets("f").Cells(i, 4) = filCount
i = i + 1
Next
Range("a1").Activate
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
Sub FaFCount(Fldr As Object, fldCount As Integer, filCount As Integer, indx
As Integer)
Dim sflds As Object
Dim sbfd As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set sflds = fso.GetFolder Fldr.subfolders '<----SYNTAX ERROR!!!!!!!!!
For Each sbfd In sflds
fldCount = fldCount + sbfd.subfolders.Count
filCount = filCount + sbfd.Files.Count
Next sbfd
If sbfd.subfolders.Count > 0 Then
FaFCount sbfd.subfolders, fldCount, filCount, indx
End If
End Sub
