excluding folder paths with spaces

  • 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:


  • It looks like this line in ListMyFiles

    Code
    1. 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
    1. 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
    1. If LCase(vArray(i)) = LCase(sVal) Then
  • 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:

  • 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
    1. If Right(mySourcePath, 1) <> "\" Then mySourcePath = mySourcePath + "\"

    best place is probably here:

    Code
    1. Set myObject = New Scripting.FileSystemObject
    2. If Right(mySourcePath, 1) <> "\" Then mySourcePath = mySourcePath + "\"
    3. Set mySource = myObject.GetFolder(mySourcePath)
  • 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?

  • 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"

  • 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.