excluding folder paths with spaces

anmac1789 · Jan 21st 2021

Hello, this post is in response to the thread posted here:

List files macro, need to add exclude specific subfolder in it - Excel General - OzGrid Free Excel/VBA Help Forum

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

gijsmo · Jan 21st 2021

It looks like this line in ListMyFiles

Code

sf = LCase(Right(mySubFolder.Path, Len(mySubFolder.Path) - Len(mySourcePath) - 1))

is truncating the first letter of the folder, also it is converting the result to lower case

Then the IndexStrArray function is comparing a lower case string to whatever you typed into cell A3

Try changing to:

Code

sf = Trim(Right(mySubFolder.Path, Len(mySubFolder.Path) - Len(mySourcePath)))

The 'Trim' part isn't strictly necessary but won't hurt.

and then in the IndexStrArray function change the compare line to:

Code

If LCase(vArray(i)) = LCase(sVal) Then

anmac1789 · Jan 21st 2021

it almost works except for one thing. I have attached the same format except its for a different folder for which it doesn't work for, Basically, it shouldn't show any list because there is no file under the "a" folder. I have adjusted the code to take into your substitutions. Please take a look:

updated code:

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 = Trim(Right(mySubFolder.Path, Len(mySubFolder.Path) - Len(mySourcePath)))
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 LCase(vArray(i)) = LCase(sVal) Then
IndexStrArray = i
Exit Function
End If
Next i
Minus1:
IndexStrArray = -1
End Function

Display More

anmac1789 · Jan 21st 2021

I think the problem is occurring with "asf() = Split(Replace(excludedSubfolders, ", ", ","), ",")"

gijsmo · Jan 22nd 2021

The problem I think is not including the final backslash in the parent path, this should be fixed by adding this line in the ListMyFiles sub:

Code

If Right(mySourcePath, 1) <> "\" Then mySourcePath = mySourcePath + "\"

best place is probably here:

Code

Set myObject = New Scripting.FileSystemObject
If Right(mySourcePath, 1) <> "\" Then mySourcePath = mySourcePath + "\"
Set mySource = myObject.GetFolder(mySourcePath)

anmac1789 · Jan 22nd 2021

so basically, your saying that I just need to add a "\" to the parent path ?? Also, what if i want to get all subfolders within a parent directory but I want to exclude a deeper nested subfolder ? how can I adjust the code?

anmac1789 · Jan 22nd 2021

in addition to post #6 - it seems like the problem now is for longer parent folder paths...the files do not even show up..the path length is "C:\Users\[username]\Downloads\duracell 4GB"

gijsmo · Jan 22nd 2021

The code I suggested in my previous post will fix the issue of the "\" on the end of the parent folder - if you don't add it to the parent folder in cell A1, the code will simply add it for you. This seems to work OK for all the test cases I've created at my end.

Your other questions are now going well beyond the original problem and probably require a new thread.

anmac1789 · Jan 22nd 2021

i see, okay i will make a new thread

anmac1789 · Jan 28th 2021

this only works for 1st level directories, not deeper nested subfolder names and not even deeper nested subfolder paths!

Share