Hello, this post is in response to the thread posted here:
and also here: excluding folder paths - Excel General - OzGrid Free Excel/VBA Help Forum
My question is that this code does not take into account excluding folder paths with spaces. For example, searching path "C:\KINGSTON 240GB SSD" and lets say I have a subfolder called "Documents" inside that parent folder. If I am typing "Documents" (without quotes) in cell A3 and click "list files in subfolders less exception list" then I get a list of all files within "Documents" folder. Which is not what this code is supposed to do. How can I fix this ?
As proof here is my code and a screenshot of the folder structure:
Code
Option Explicit
'http://www.ozgrid.com/forum/showthread.php?t=158478
Dim iRow As Long
Sub ListFiles()
Dim lRow As Long
iRow = 11
lRow = Range("B" & Rows.Count).End(xlUp).Row
If lRow >= iRow Then
Range("B" & iRow & ":E" & Range("B" & Rows.Count).End(xlUp).Row).Clear
End If
Call ListMyFiles(Range("A1"), Range("A2"), Range("A3"))
Application.GoTo Range("B3"), True
End Sub
Sub ListMyFiles(mySourcePath As String, IncludeSubfolders As String, _
Optional excludedSubfolders As String = "")
Dim myObject As Scripting.FileSystemObject
Dim mySource As Scripting.Folder, myFile As Variant
Dim iCol As Integer
Dim mySubFolder As Scripting.Folder, v As Variant
Dim asf() As String, sf As String
asf() = Split(Replace(excludedSubfolders, ", ", ","), ",")
Set myObject = New Scripting.FileSystemObject
Set mySource = myObject.GetFolder(mySourcePath)
On Error Resume Next
For Each myFile In mySource.Files
iCol = 1
Cells(iRow, iCol).Value = myFile.Path
'iCol = iCol + 1
'Cells(iRow, iCol).Value = myFile.Name
iRow = iRow + 1
Next
'For Each mySubFolder In mySource.subfolders
'Cells(iRow, iCol).Value = mySubFolder.Path
'iCol = iCol + 1
'Cells(iRow, iCol).Value = mySubFolder.Name
'iRow = iRow + 1
'Next
If IncludeSubfolders Then
For Each mySubFolder In mySource.SubFolders
If excludedSubfolders = "" Then
Call ListMyFiles(mySubFolder.Path, False)
Else
sf = LCase(Right(mySubFolder.Path, Len(mySubFolder.Path) - Len(mySourcePath) - 1))
If IndexStrArray(asf(), sf) = -1 Then
Call ListMyFiles(mySubFolder.Path, True)
End If
End If
Next
End If
End Sub
'val is not case sensitive
Function IndexStrArray(vArray() As String, sVal As String) As Long
Dim v As Variant, i As Long
On Error GoTo Minus1
For i = 0 To UBound(vArray)
If vArray(i) = sVal Then
IndexStrArray = i
Exit Function
End If
Next i
Minus1:
IndexStrArray = -1
End Function
Display More