MS Office Forum / Word / Programming / February 2004
Get total folder+subfolder size in VBA
|
|
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
|
|
|