Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / February 2004

Tip: Looking for answers? Try searching our database.

Get total folder+subfolder size in VBA

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Mark Tangard - 20 Feb 2004 13:19 GMT
Anybody know a way to do the VBA equivalent of clicking File>Properties
on a folder in Windows Explorer?

I'd like to get the total number of files, and their combined size,
under a given top-level folder.  Using the FileSearch object works OK
(if slowly) for total size, but it counts folders as files in the file
count, and I don't see a property of FileSearch that can be tested as to
type.

Any clues?  TIA.
--
Mark Tangard, Microsoft Word MVP
"Life is nothing if you're not obsessed." --John Waters
Peter Hewett - 20 Feb 2004 14:34 GMT
Hi Mark

Try the Scripting FileSystemObject. You'll still have to do the donkey work
of iterating subfolders etc.

Here's some code I wrote for something else. I've hacked it quickly but not
really tested it after hacking. It used to work! It's recursive, so just
point it at the top level folder and away it goes. I've inserted a cludge
to add up the file size.

You'll need to make a project reference to the "Microsoft Scripting
Runtime" library (scrrun.dll).

Const mcBasePath As String = "F:\My Templates\"
Const mcFileTypeMask As String = ".doc"
Private mlngTotal As Long

Private Sub Worker()
   Dim fsoTemp As Scripting.FileSystemObject
   Dim filStartingFolder As Scripting.Folder

   ' Iterate from base folder
   Set fsoTemp = New Scripting.FileSystemObject
   Set filStartingFolder = fsoTemp.GetFolder(mcBasePath)
   ProcessFilesInFolder filStartingFolder
End Sub         ' Worker

Private Sub ProcessFilesInFolder(ByVal _
 folCurrentFolder As Scripting.Folder)
   Dim folSubFolder As Scripting.Folder
   Dim filToProcess As Scripting.File

   ' Process files in the current folder
   For Each filToProcess In folCurrentFolder.Files

       ' Only process files of the appropriate file type
       If LCase$(Right$(filToProcess.Name, _
         Len(mcFileTypeMask))) = mcFileTypeMask Then

               ' Total up the file sizes here
           mlngTotal = mlngTotal + filToProcess.Size

       End If
   Next filToProcess

   ' Now process files in any subfolders of the current folder
   For Each folSubFolder In folCurrentFolder.SubFolders
       ProcessFilesInFolder folSubFolder
   Next
End Sub         ' ProcessFilesInFolder

HTH + Cheers - Peter

Mark Tangard <Mark@NoMailPlease_Tangard.com> wrote in news:eKCsmQ79DHA.632
@TK2MSFTNGP12.phx.gbl:

> Anybody know a way to do the VBA equivalent of clicking File>Properties
> on a folder in Windows Explorer?
[quoted text clipped - 9 lines]
> Mark Tangard, Microsoft Word MVP
> "Life is nothing if you're not obsessed." --John Waters
Perry - 20 Feb 2004 18:21 GMT
Mark,

You'll have to use the FindFirstFile() and FindNextFile() API calls to
the kernel32 dll.
These have to be called in a recursive way to get you information
on *all* subdirectories under a given rootfolder.

The below result took me a second (1.06 seconds)

2147483634 bytes    (size of all files)
12630 files                (number of files)
929 folders            (number of folders)

These API's perform super.

Shout, if y're interested in a simple example ...

Krgrds,
Perry

> Anybody know a way to do the VBA equivalent of clicking File>Properties
> on a folder in Windows Explorer?
[quoted text clipped - 9 lines]
> Mark Tangard, Microsoft Word MVP
> "Life is nothing if you're not obsessed." --John Waters
Perry - 20 Feb 2004 19:11 GMT
Oke, here's the example
Others may benefit ....

Userform containing:
a Textbox for the rootfolder of yr choosing, called: TextBox1
a Commandbutton, called: Commandbutton1

Some variables are redundant .... i've copied them
straight from a project and simplified as much as possible
you'll have to get rid of them yrself ...

If the abovementioned controls are there, it'll compile/run
and use GetTickCount() API to measure the results against
other algorithms you may have.

I've left out other fancy features like: choosing the rootfolder ...etc bla
y'll have to type the rootfolder in TextBox1 in below example.

It's the search mechanism/algorithm you will like ....

Krgrds,
Perry

==begin Userform module code

Private Const vbDot = 46
Private Const MAXDWORD = &HFFFFFFFF
Private Const MAX_PATH = 260
Private Const INVALID_HANDLE_VALUE = -1
Private Const FILE_ATTRIBUTE_DIRECTORY = &H10

Dim lSize       As Long
Dim lFileNum    As Long
Dim lFolders    As Long

Private Type FILETIME
  dwLowDateTime As Long
  dwHighDateTime As Long
End Type

Private Type WIN32_FIND_DATA
  dwFileAttributes As Long
  ftCreationTime As FILETIME
  ftLastAccessTime As FILETIME
  ftLastWriteTime As FILETIME
  nFileSizeHigh As Long
  nFileSizeLow As Long
  dwReserved0 As Long
  dwReserved1 As Long
  cFileName As String * MAX_PATH
  cAlternate As String * 14
End Type

Private Type FILE_PARAMS
  bRecurse As Boolean
  sFileRoot As String
  sFileNameExt As String
  sResult As String
  sMatches As String
  Count As Long
End Type

Private Declare Function FindClose Lib "kernel32" (ByVal hFindFile As Long)
As Long

Private Declare Function FindFirstFile Lib "kernel32" _
  Alias "FindFirstFileA" _
 (ByVal lpFileName As String, _
  lpFindFileData As WIN32_FIND_DATA) As Long

Private Declare Function FindNextFile Lib "kernel32" _
  Alias "FindNextFileA" _
 (ByVal hFindFile As Long, _
  lpFindFileData As WIN32_FIND_DATA) As Long

Private Declare Function GetTickCount Lib "kernel32" () As Long

Private Sub CommandButton1_Click()
Dim FP As FILE_PARAMS

   'Point to local occurance of error object
   'and handle locally
   On Local Error GoTo InitError

   lSize = 0
   lFileNum = 0
   lFolders = 0
   Me.TextBox1
   With FP
       .sFileRoot = Me.TextBox1    '<< ROOTFOLDER
       .sFileNameExt = "*"
       .bRecurse = True
   End With

   RecursiveFileSearch FP

   MsgBox "Size of all files: " & lSize & vbCr & _
       "Number of files: " & lFileNum & vbCr & _
       "Number of folders: " & lFolders
ExitHere:
   Exit Sub

InitError:
   MsgBox "catch the error"
   Resume ExitHere
End Sub

Private Sub RecursiveFileSearch(FP As FILE_PARAMS)
   Dim WFD As WIN32_FIND_DATA
   Dim hFile As Long
   Dim sRoot As String
   Dim spath As String
   Dim fso As New FileSystemObject
   Dim MyFolder As Folder

   sRoot = QualifyPath(FP.sFileRoot)
   spath = sRoot & FP.sFileNameExt

   hFile = FindFirstFile(spath, WFD)
   'if valid ...
   If hFile <> INVALID_HANDLE_VALUE Then
       Do

           If (WFD.dwFileAttributes And FILE_ATTRIBUTE_DIRECTORY) And _
               Asc(WFD.cFileName) <> vbDot Then

               sTmp = TrimNull(WFD.cFileName)
               If (sTmp <> ".") And (sTmp <> "..") Then
                   FP.sFileRoot = sRoot & sTmp    'adjust root
                   FP.Count = FP.Count + 1            'new count
                   Set MyFolder = fso.GetFolder(sRoot & sTmp)
                   lFileNum = lFileNum + MyFolder.Files.Count
                   lSize = lSize + MyFolder.Size
                   lFolders = FP.Count

                   RecursiveFileSearch FP  '<<< here's the recursive
(intrinsic call to itself) part
               End If
           End If
       Loop While FindNextFile(hFile, WFD)
       hFile = FindClose(hFile)

   End If
   Set fso = Nothing
   Exit Sub
Foutje:
   Resume Next
End Sub

'******************************************************
Private Function QualifyPath(spath As String) As String
  If Right$(spath, 1) <> "\" Then
        QualifyPath = spath & "\"
  Else: QualifyPath = spath
  End If
End Function

==end Userform module code

> Mark,
>
[quoted text clipped - 29 lines]
> > Mark Tangard, Microsoft Word MVP
> > "Life is nothing if you're not obsessed." --John Waters
Mark Tangard - 21 Feb 2004 10:25 GMT
Perry,

Ooooh yeah....

I can tell this'll be 100+ times faster than my prior attempt. However,
I'm getting an error here (on the last line shown), saying it doesn't
know what TrimNull is.  One of your children?

If hFile <> INVALID_HANDLE_VALUE Then
    Do
        If (WFD.dwFileAttributes And FILE_ATTRIBUTE_DIRECTORY) And _
          Asc(WFD.cFileName) <> vbDot Then
            sTmp = TrimNull(WFD.cFileName)

--
Mark Tangard, Microsoft Word MVP
Note well: MVPs do not work for Microsoft.
"Life is nothing if you're not obsessed." --John Waters

> Oke, here's the example
> Others may benefit ....
[quoted text clipped - 188 lines]
>>>Mark Tangard, Microsoft Word MVP
>>>"Life is nothing if you're not obsessed." --John Waters
Perry - 21 Feb 2004 13:22 GMT
oops
This fast copying/pasting ...

Here ya go

'******************************************************
Private Function TrimNull(startstr As String) As String
   Dim pos As Integer
   'bepaal waar NULL karakter zich bevindt
   pos = InStr(startstr, Chr$(0))
   If pos Then
       'linker gedeelte voor NULL karakter zoeken we ...
       TrimNull = Left$(startstr, pos - 1)
       Exit Function
   End If

   TrimNull = startstr '-> geef terug aan functie
End Function

PdL

> Perry,
>
[quoted text clipped - 207 lines]
> >>>Mark Tangard, Microsoft Word MVP
> >>>"Life is nothing if you're not obsessed." --John Waters
Mark Tangard - 22 Feb 2004 13:29 GMT
Hmm, OK, got that in.  But what's happening now is, no matter what
folder I ask it to process, it's spitting out extremely wrong answers.
They're so wrong, I can't even see a pattern.  For example:

For a folder and its 3 subfolders and 2 sub-subfolders, which combined
total 17MB in 188 files, the macro reports a total of 21MB in 28 files.

For a folder and its one subfolder, all combined containing 259KB in 8
files, it reports 54KB and a total of *one* file.  (The only clue here
is that 54KB is the size of the one file in the one subfolder.)

I thought at first I might've bothced the mild editing I did to turn the
CommandButton1_Click code into an ordinary sub (I'm using this in a
regular macro, not in a userform); but I put back the unedited code and
the same result occurs.

What could be at the root of this??

One other thing: The code for the CommandButton1_Click begins with the
following.  Note line 6, which reads simply Me.TextBox1.  Was something
else supposed to be there?

Dim FP As FILE_PARAMS, strSize As String
On Local Error GoTo InitError
lSize = 0
lFileNum = 0
lFolders = 0
Me.TextBox1
With FP
    .sFileRoot = Me.TextBox1    '<< ROOTFOLDER
    .sFileNameExt = "*"
    .bRecurse = True
End With
RecursiveFileSearch FP
         '<-----etc.

Thanks again for helping out with this.

--
Mark Tangard, Microsoft Word MVP
Note well: MVPs do not work for Microsoft.
"Life is nothing if you're not obsessed." --John Waters
Perry - 27 Feb 2004 16:20 GMT
> following.  Note line 6, which reads simply Me.TextBox1.  Was something
> else supposed to be there?

You're very true. Delete that line of code ... This line was a result of the
copy
paste action I had to perform to delete some redudant lines. Delete that
line.
No influence on the erraneous results y're getting.

Can you kick in the FindFistFile/FindNextFile loop passage of the code?
And the subroutine or code passage in which you transfer the file
attributes?

Don't have any problems here ... I've used this code in several projects
and results are ok.

Krgrds,
Perry

> Hmm, OK, got that in.  But what's happening now is, no matter what
> folder I ask it to process, it's spitting out extremely wrong answers.
[quoted text clipped - 38 lines]
> Note well: MVPs do not work for Microsoft.
> "Life is nothing if you're not obsessed." --John Waters
Mark Tangard - 29 Feb 2004 14:39 GMT
Perry,

OK, here's the FindFirst/Find Next, and following it is the 2-procedure
chain that calls it.

And for some reason, today (having not touched the code), instead of
giving the quick, if incorrect, result, it's running for several minutes
and (to judge from the value of certain variables when I hit Break)
iterating through all the folders on the hard drive.

Thanks again,
MT

Private Sub RecursiveFileSearch(FP As FILE_PARAMS)
Dim WFD As WIN32_FIND_DATA
Dim hFile As Long
Dim sRoot As String
Dim spath As String
Dim fso As New FileSystemObject
Dim MyFolder As Folder
Dim sTmp As String
sRoot = QualifyPath(FP.sFileRoot)
spath = sRoot & FP.sFileNameExt
hFile = FindFirstFile(spath, WFD)
'if valid ...
If hFile <> INVALID_HANDLE_VALUE Then
    Do
        If (WFD.dwFileAttributes And FILE_ATTRIBUTE_DIRECTORY) _
          And Asc(WFD.cFileName) <> vbDot Then
            sTmp = TrimNull(WFD.cFileName)
            If (sTmp <> ".") And (sTmp <> "..") Then
                FP.sFileRoot = sRoot & sTmp    'adjust root
                FP.Count = FP.Count + 1            'new count
                Set MyFolder = fso.GetFolder(sRoot & sTmp)
                lFileNum = lFileNum + MyFolder.Files.Count
                lSize = lSize + MyFolder.Size
                lFolders = FP.Count
                RecursiveFileSearch FP
            End If
        End If
    Loop While FindNextFile(hFile, WFD)
    hFile = FindClose(hFile)
End If
Set fso = Nothing
Exit Sub
Foutje:
Resume Next
End Sub

'============

Sub A_021747()
GetFolderData "C:\MyDir"
End Sub

'============

Sub GetFolderData(DirName)
Dim FP As FILE_PARAMS
'Point to local occurrence of error object and handle locally
On Local Error GoTo InitError
lSize = 0
lFileNum = 0
lFolders = 0
DirName = ""
With FP
    .sFileRoot = DirName    '<< ROOTFOLDER
    .sFileNameExt = "*"
    .bRecurse = True
End With
On Error GoTo 0

RecursiveFileSearch FP
MsgBox "Size of all files: " & lSize & vbCr & "Number of files: " _
   & lFileNum & vbCr & "Number of folders: " & lFolders

ExitHere:
Exit Sub
InitError:
MsgBox "catch the error"
Resume ExitHere
End Sub

>>following.  Note line 6, which reads simply Me.TextBox1.  Was something
>>else supposed to be there?
[quoted text clipped - 57 lines]
>>Note well: MVPs do not work for Microsoft.
>>"Life is nothing if you're not obsessed." --John Waters
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.