Hi
I have debugged the code and I am experiencing problems with duplicating/triplicating results and empty sufolders being displayed as empty as only the parent folder should be showing in results.
I would really appreciate if you could spare your valuable time to have a look at this issue.
Thank you.
Here a summary:
I have created in my file location (C:\ Documents) the below listed 6 folders as follows:
Folders no. 1+2 contain spreadsheets - no subfolders.
Folders no. 3+4 contain spreadsheets - with subfolders.
Folder no 5 contains no spreadsheets – no subfolders.
Folder no 6 contains no spreadsheets – with subfolders.
The program code should return folders names/location of 1 - 4 containing all the found spreadsheets. Folders no. 5+6 contain no spreadsheets but the result should still display the folder name as being empty (subfolders will not be shown when empty).
Folder 1
Folder 2
Folder 3
Folder 4
Folder 5
Folder 6
The program results below shows that it works however it double or triple counts the searches.
For example results as follows:
'###
Questions found in function “Public Function SearchForFile()”:
Debug.Print "Found colFiles.Count " & colFiles.Count & " files."
Debug.Print "Found colEmptyFolders.Count " & colEmptyFolders.Count & " folders."
Result for above question
Found colFiles.Count 18 files.
Found colEmptyFolders.Count 26 folders.
True Result should be
Found colFiles.Count 9 files.
Found colEmptyFolders.Count 6 folders.
‘##
Displaying detailed Result – It is duplicating results
'Display list of all files found - including the folder name/
For Each vFile In colFiles
Debug.Print "File Found: "; vFile
lnFileIsFound = True
Next vFile
File Found: C:\Documents\Folder 1\Spreadsheet 1.xlsx
File Found: C:\Documents\Folder 1\Spreadsheet 2.xlsx
File Found: C:\Documents\Folder 2\Spreadsheet 3.xlsx
File Found: C:\ Documents\Folder 3\Spreadsheet 4.xlsx
File Found: C:\Documents\Folder 3\SubFolder 1\Spreadsheet 6.xlsx
File Found: C:\Documents\Folder 3\SubFolder 2\Spreadsheet 5.xlsx
File Found: C:\Documents\Folder 4\Spreadsheet 7.xlsx
File Found: C:\Documents\Folder 4\SubFolder 1\Spreadsheet 8.xlsx
File Found: C:\Documents\Folder 4\SubFolder 2\Spreadsheet 9.xlsx
File Found: C:\Documents\Folder 1\Spreadsheet 1.xlsx
File Found: C:\Documents\Folder 1\Spreadsheet 2.xlsx
File Found: C:\Documents\Folder 2\Spreadsheet 3.xlsx
File Found: C:\Documents\Folder 3\Spreadsheet 4.xlsx
File Found: C:\Documents\Folder 3\SubFolder 1\Spreadsheet 6.xlsx
File Found: C:\Documents\Folder 3\SubFolder 2\Spreadsheet 5.xlsx
File Found: C:\Documents\Folder 4\Spreadsheet 7.xlsx
File Found: C:\Documents\Folder 4\SubFolder 1\Spreadsheet 8.xlsx
File Found: C:\Documents\Folder 4\SubFolder 2\Spreadsheet 9.xlsx
Empty Result – It is duplicating and showing subfolders as empty it should only show parent folder name and not empty subfolders
'Display list of all folders found that did NOT contain files being searched for.
For Each vFile In colEmptyFolders
Debug.Print "Empty Folder: "; vFile
blnFileIsFound = True
Next vFile
Empty Folder: C: \DocumentsEmpty Folder: C:\ DocumentsEmpty Folder: C:\Documents\Folder 6Empty Folder: C:\Documents\Folder 6Empty Folder: C:\Documents\Folder 6\SubFolder 1Empty Folder: C:\Documents\Folder 6\SubFolder 1Empty Folder: C:\Documents\Folder 6\SubFolder 2Empty Folder: C:\Documents\Folder 6\SubFolder 2Empty Folder: C:\Documents\Folder 6\SubFolder 1Empty Folder: C:\Documents\Folder 6\SubFolder 1Empty Folder: C:\Documents\Folder 6\SubFolder 2Empty Folder: C:\Documents\Folder 6\SubFolder 2Empty Folder: C:\Documents\Folder 5Empty Folder: C:\Documents\Folder 5Empty Folder: C:\Documents\Folder 6Empty Folder: C:\Documents\Folder 6Empty Folder: C:\Documents\Folder 6\SubFolder 1Empty Folder: C:\Documents\Folder 6\SubFolder 1Empty Folder: C:\Documents\Folder 6\SubFolder 2Empty Folder: C:\Documents\Folder 6\SubFolder 2Empty Folder: C:\Documents\Folder 6\SubFolder 1Empty Folder: C:\Documents\Folder 6\SubFolder 1Empty Folder: C:\Documents\Folder 6\SubFolder 2Empty Folder: C:\Documents\Folder 6\SubFolder 2Empty Folder: C:\Documents\Folder 5Empty Folder: C:\Documents\Folder 5
The code functions are as follows:
Code:
Public Function SearchForFile()
Dim colFiles As New Collection
Dim vFile As Variant
Dim blnFileIsFound As Boolean
Set colFoundFiles = New Collection
Set colEmptyFolders = New Collection
blnFileIsFound = False
RecursiveDir colFiles, "C:\Documents", "*.xls", True
Debug.Print "Found colFiles.Count " & colFiles.Count & " files."
Debug.Print "Found colEmptyFolders.Count " & colEmptyFolders.Count & " folders."
'Display list of all files found - including the folder name/
For Each vFile In colFiles
Debug.Print "File Found: "; vFile
lnFileIsFound = True
Next vFile
'Display list of all folders found that did NOT contain files being searched for.
For Each vFile In colEmptyFolders
Debug.Print "Empty Folder: "; vFile
blnFileIsFound = True
Next vFile
If Not blnFileIsFound Then
MsgBox "File Not Found.:" & vFile
End If
End Function
Code:
Public Function RecursiveDir (colFiles As Collection, _
strFolder As String, strFileSpec As String, bIncludeSubfolders As Boolean)
On Error GoTo RecursiveDir_Fail
Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant
Dim blnFound As Boolean
Dim lItems As Long
'set initial start to false
blnFound = False
Set colFolders = New Collection
' Make sure that the folder names we are processing end with the slash character.
'if it does do nothing. if it does not add
If Right(strFolder, 1) = "\" Or Right(strFolder, 1) = "/" Then
'Ignore
Else
colFolders.Add strFolder & "\"
End If
'Add files in strFolder matching strFileSpec to colFiles, to build the collections
strFolder = TrailingSlash(strFolder)
strTemp = Dir(strFolder & strFileSpec)
Do While strTemp <> vbNullString
colFiles.Add strFolder & strTemp
colFoundFiles.Add strFolder & "|" & strTemp
blnFound = True
strTemp = Dir
Loop
If blnFound = True Then
For lItems = 1 To colFolders.Count
If colFolders(lItems) = strFolder Then
colFolders.Remove (lItems)
End If
Next lItems
Else
colEmptyFolders.Add strFolder
End If
If bIncludeSubfolders Then
'Fill colFolders with list of subdirectories of strFolder
strTemp = Dir(strFolder, vbDirectory)
Do While strTemp <> vbNullString
If (strTemp <> ".") And (strTemp <> "..") Then
If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0 Then
colFolders.Add strFolder & strTemp
End If
End If
strTemp = Dir
Loop
' Calling RecursiveDir function to seach each subfolder in colFolders
' doing another search for blanks where excel file needed not found.
For Each vFolderName In colFolders
Debug.Print "Searching: " & vFolderName
strFolder = vFolderName
Call RecursiveDir(colFiles, strFolder, strFileSpec, True)
Next vFolderName
End If
Exit_RecursiveDir:
Exit Function
RecursiveDir_Fail:
Debug.Print Err.Number & vbTab & Err.Description
If Err.Number = 52 Then ' Permissions denied on system folder...
Resume Next
ElseIf Err.Number = 53 Then ' File Not Found
Resume Next
Else
MsgBox "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & vbTab & Err.Description
Resume Exit_RecursiveDir
End If
End Function
