exclude folder paths list

[anmac1789]

hello, everyone I have a vba code where I am trying to incorporate a exclude folder list. I did not find a specific function to exclude folder path in excel vba or microsoft docs for excel vba.

For example, lets say I have folders:
C:/test with spaces/
C:/test with spaces/subfolder 1/
C:/test with spaces/subfolder 2/
C:/test with spaces/subfolder 3/
C:/test with spaces/subfolder 1/2ndlevelsubfolder1
C:/test with spaces/subfolder 1/2ndlevelsubfolder1-1
C:/test with spaces/subfolder 2/2ndlevelsubfolder2
C:/test with spaces/subfolder 2/2ndlevelsubfolder2-2
C:/test with spaces/subfolder 3/2ndlevelsubfolder3
C:/test with spaces/subfolder 3/2ndlevelsubfolder3-3

I want to create an exclude folder list that can exclude all files under the folder path and as well as exclude the folder itself from the search list. For example, lets say I wanted to exclude all files under "C:/test with spaces/subfolder 2/2ndlevelsubfolder2-2" which has file2.txt inside...so i want to exclude 2 things:

1. file2.txt
2. C:/test with spaces/subfolder 2/2ndlevelsubfolder2-2\ itself

I want this functionality to loop for all files and all subfolders within the specified exclude folder. Thank you & I hope it makes some sense


Option Explicit
Sub SomeSub()
Call GetFiles("\\?\C:\test with spaces") 'attach "\\?\" at the beginning for long folder path names! ex..'GetFiles("\\?\INSERT..." 'can also list multiple "Call GetFiles("\\?\[insert new folder path here]")" to list multiple folder paths all at once
End Sub
Sub GetFiles(ByVal path As String)
Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")


Dim folder As Object
Set folder = FSO.GetFolder(path)


Dim SubFolder As Object
Dim file As Object


For Each SubFolder In folder.Subfolders
GetFiles (SubFolder.path)
Next SubFolder


Range("A1") = "parent folder"
'Range("A1").Offset(0, 1) = "FILE/FOLDER PATH"
Range("A1").Offset(0, 3) = "FILE or FOLDER"
Range("A1").Offset(0, 4) = "DATE CREATED"
Range("A1").Offset(0, 5) = "DATE MODIFIED"
Range("A1").Offset(0, 6) = "SIZE"
Range("A1").Offset(0, 7) = "TYPE"

Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Replace(folder, "\\?\", "")
Range("A" & Rows.Count).End(xlUp).Offset(0, 1) = Replace(folder, "\\?\", "")
Range("A" & Rows.Count).End(xlUp).Offset(0, 2) = folder.Name
Range("A" & Rows.Count).End(xlUp).Offset(0, 3) = "FOLDER"
Range("A" & Rows.Count).End(xlUp).Offset(0, 4) = folder.datecreated
Range("A" & Rows.Count).End(xlUp).Offset(0, 5) = folder.DateLastModified


'For Each SubFolder In folder.Subfolders
'Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Replace(subfolder.path, "\\?\", "")
'Range("A" & Rows.Count).End(xlUp).Offset(0, 1) = Replace(folder, "\\?\", "")
'Range("A" & Rows.Count).End(xlUp).Offset(0, 2) = subfolder.Name
'Range("A" & Rows.Count).End(xlUp).Offset(0, 3) = "FOLDER"
'Range("A" & Rows.Count).End(xlUp).Offset(0, 4) = subfolder.datecreated
'Range("A" & Rows.Count).End(xlUp).Offset(0, 5) = subfolder.DateLastModified
'Next SubFolder


'For Each file In folder.Files
'Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Replace(file.path, "\\?\", "")
'Range("A" & Rows.Count).End(xlUp).Offset(0, 1) = Replace(folder, "\\?\", "")
'Range("A" & Rows.Count).End(xlUp).Offset(0, 2) = file.Name
'Range("A" & Rows.Count).End(xlUp).Offset(0, 3) = "FILE"
'Range("A" & Rows.Count).End(xlUp).Offset(0, 4) = file.datecreated
'Range("A" & Rows.Count).End(xlUp).Offset(0, 5) = file.DateLastModified
'Range("A" & Rows.Count).End(xlUp).Offset(0, 6) = file.Size
'Range("A" & Rows.Count).End(xlUp).Offset(0, 7) = file.Type
'Next file


With Range("E:F")
.NumberFormat = "dddd mmmm dd, yyyy H:mm:ss AM/PM" 'long file date and time
End With


Set FSO = Nothing
Set folder = Nothing
Set SubFolder = Nothing
Set file = Nothing


End Sub

[snb]

Avoid spaces in foldernames.


Sub M_snb()
c00="text2"
sn=split(createobject("wscript.shell").exec("cmd /c dir ""C:/test with spaces/*.*"" /b/s/a-d").stdout.readall,vbcrlf)

sp=filter(sn,c00)
for each it in sp
sn=filter(sn,replace(it,c00,""),0)
next

msgbox join(sn,vbcrlf)
End sub

[anmac1789]

I'm not sure what your trying to do here.... most of my files in my C: drive do have spaces in the filename and folder name. The above folder was just an example..

[snb]

Test the macro.
Better your life.

[anmac1789]

ok, the above macro gives a compile error: variable not defined

[SamT]

snb doesn't use "Option Explicit," so he doesn't have to declare variables, because he never misspells variable names because he always uses the same variable names

Add this line at the top of the sub
dim c00, sn, it, spWhich will declare those names as Variant Type Variables, the same as they are in his code without "Option Explicit."

BTW, I disagree with his suggestion, because, heaven forbid, you could change a Name already in use by some other code/program. Windows OS uses many Folders with spaces in their names so it's obvious that there is no inherent danger in using spaces.

Exclusion code like you want must be custom designed for each application. I would write a Function to test each Name provided and return a True if the Name is excluded. Then your main code can look like

Sub main()
For each X in Y
If Not Excluded(X.Name) then
Process X
End If
Next


Function Excluded(Name as String) as Boolean
If Name is not to be processed Then Excluded = True
End Function

[anmac1789]

im having a difficult time with understanding the variables you use in your code without the actual code put in. Could you give me an example and go through what each line does as I am new to programming in excel vba and I cannot find for the life of me searching for over 2 weeks understand setting up and exclusion paths list on my own

[snb]

This programming task isn't suited for VBA beginners.

[anmac1789]

i just wanted to know how to code exclude deeper nested folder paths but it seems like there isn't a function or code thats explicitly for exclusion...

well..maybe I dont need to understand it to use it..if it can be coded for such a thing. I am trying to look at collections right now because I have tried arrays but it's strange in the way how I have to define it by the size and don't know if i can use filesystemobjects with arrays or collections such as .datecreated,datemodified, .size, .path etc..whilst also having to exclude a specific folder path..

[SamT]

No "variables" included below:


Exclusion code like you want must be custom designed for each use.

[anmac1789]

is it possible to split a folder path with the delimiter "" and then take the last path where the file is and set that as a variable nsme and then ...im lost thats where i get confused. What do I do with a broken up subfolder path?? this is my thought process for now..

[snb]

If you remove 'Option Explicit' and run my code you 'always get what you want'.

[anmac1789]

This post is for snb, so I removed option explicit. I ran your code but all I get is an empty msgbox

[snb]

Of course you have to adapt 'C:/test with spaces' to an existing foldername.
And 'text2' to an existing filename.

[Paul_Hossler]

I want this functionality to loop for all files and all subfolders within the specified exclude folder. Thank you & I hope it makes some sense

and


I want to create an exclude folder list that can exclude all files under the folder path and as well as exclude the folder itself from the search list. For example, lets say I wanted to exclude all files under "C:/test with spaces/subfolder 2/2ndlevelsubfolder2-2" which has file2.txt inside...so i want to exclude 2 things:

So you want to pass a folder name ("C:/test with spaces/subfolder 2/2ndlevelsubfolder2-2") and get back a recursive list of folders and files within and including the passed folder?

"C:/test with spaces/subfolder 2/2ndlevelsubfolder2-2"
"file2.txt"
"C:/test with spaces/subfolder 2/2ndlevelsubfolder2-2\Lower"

etc.


'attach "\\?" at the beginning for long folder path names! ex..'GetFiles("\\?\INSERT..."

Don't understand why you need the "\\?" part

[anmac1789]

okay so basically let me explain. Here are the list of files and folders in "C:\test with spaces"




c:\test with spaces\subfolder 1\2ndlevelsubfolder1


c:\test with spaces\subfolder 1\2ndlevelsubfolder1\file1.txt


c:\test with spaces\subfolder 1\2ndlevelsubfolder1-1


c:\test with spaces\subfolder 1\2ndlevelsubfolder1-1\file1.txt


c:\test with spaces\subfolder 1


c:\test with spaces\subfolder 1\file1-1.txt


c:\test with spaces\subfolder 1\file1.txt


c:\test with spaces\subfolder 2\2ndlevelsubfolder2


c:\test with spaces\subfolder 2\2ndlevelsubfolder2\file2.txt


c:\test with spaces\subfolder 2\2ndlevelsubfolder2-2


c:\test with spaces\subfolder 2\2ndlevelsubfolder2-2\file2.txt


c:\test with spaces\subfolder 2


c:\test with spaces\subfolder 2\file2-1.txt


c:\test with spaces\subfolder 2\file2.txt


c:\test with spaces\subfolder 3\2ndlevelsubfolder3


c:\test with spaces\subfolder 3\2ndlevelsubfolder3\file3.txt


c:\test with spaces\subfolder 3\2ndlevelsubfolder3-3


c:\test with spaces\subfolder 3\2ndlevelsubfolder3-3\file3.txt


c:\test with spaces\subfolder 3


c:\test with spaces\subfolder 3\file3-3.txt


c:\test with spaces\subfolder 3\file3.txt


c:\test with spaces


c:\test with spaces\file1.txt


c:\test with spaces\file2.txt


c:\test with spaces\file3.txt



lets say for example, I want to exclude everything in the subfolder "C:\test with spaces\subfolder 1" (1st level subfolder) including "\subfolder 1" itself AND under "c:\test with spaces\subfolder 2\2ndlevelsubfolder2-2" (2nd level subfolder). Then when I run my code it's going to display all files/folders for the above path "C:\test with spaces" ***EXCLUDING*** EVERYTHING ​under and in "C:\test with spaces\subfolder 1" & "c:\test with spaces\subfolder 2\2ndlevelsubfolder2-2". Which means to make it explicitly clear:




c:\test with spaces\subfolder 1\2ndlevelsubfolder1


c:\test with spaces\subfolder 1\2ndlevelsubfolder1\file1.txt


c:\test with spaces\subfolder 1\2ndlevelsubfolder1-1


c:\test with spaces\subfolder 1\2ndlevelsubfolder1-1\file1.txt


c:\test with spaces\subfolder 1


c:\test with spaces\subfolder 1\file1-1.txt


c:\test with spaces\subfolder 1\file1.txt


c:\test with spaces\subfolder 2\2ndlevelsubfolder2


c:\test with spaces\subfolder 2\2ndlevelsubfolder2\file2.txt


c:\test with spaces\subfolder 2\2ndlevelsubfolder2-2


c:\test with spaces\subfolder 2\2ndlevelsubfolder2-2\file2.txt


c:\test with spaces\subfolder 2


c:\test with spaces\subfolder 2\file2-1.txt


c:\test with spaces\subfolder 2\file2.txt


c:\test with spaces\subfolder 3\2ndlevelsubfolder3


c:\test with spaces\subfolder 3\2ndlevelsubfolder3\file3.txt


c:\test with spaces\subfolder 3\2ndlevelsubfolder3-3


c:\test with spaces\subfolder 3\2ndlevelsubfolder3-3\file3.txt


c:\test with spaces\subfolder 3


c:\test with spaces\subfolder 3\file3-3.txt


c:\test with spaces\subfolder 3\file3.txt


c:\test with spaces


c:\test with spaces\file1.txt


c:\test with spaces\file2.txt


c:\test with spaces\file3.txt



the coloured list of files/folders should be excluded after running the code


Don't understand why you need the "\\?" part The prefix is for long folder path names exceeding 255 character (or 260 limit) whatever the limit is

[anmac1789]

Does anyone know how to proceed ?? :banghead::banghead::banghead:

[SamT]

Exclusion code like you want must be custom designed for each application. I would write a Function to test each Name provided and return a True if the Name is excluded.

[anmac1789]

well for my code, I want those names that are excluded to not show up in the list as my code is run before the search takes place. Yes, I want to see the code that could possibly help me

[SamT]

are you talking about Windows Search?

[anmac1789]

no absolutely not, I am talking about using VBA code. What i am saying is that search button on my worksheet to retreive the list of files/folders

[SamT]

See Post # 6

[anmac1789]

So basically you are saying that using this code:


Sub main()
For each file in parentfolder.subfolders
If Not Excluded(subfolder 1.Name) then
execute all the range code with recursive getfiles
End If
Next
end sub

[anmac1789]

post #6 only checks for path names but what I am looking for is if a path name matches during subfolder search

for example, during :

for each osubfolder in directory.folder (parent directory)
if subfolder = exclud then
leave out this list of subfolders
end if
list other subfolders as normal as in the 1st post
next osubfolder


i dont know how to combine this with excluding all files within the excluded subfolder in one code

[SamT]

excluding all files within the excluded subfolder
How does your code "See" into an excluded folder? The question is not logical. (Forgive the Spockism.)

[anmac1789]

well what I want vba to see is as the code is running, check each subfolder path and file path and if it matches the folder path I want to be excluded then it excludes listing it in the main worksheet. I want to see if it can do it without listing all the folder and file paths first and then exclude those folder/file paths because it seems like that just takes more memory

[SamT]

Well, I want to be 30 years old and 6' tall. :rofl:

[anmac1789]

what?

[snb]

@SamT

No problem. Your wish is my command.:bug:

[anmac1789]

okay so getting back to topic...

[Paul_Hossler]

I'm very confused by the posts in this thread

I created a simple folder tree and recursed it

I have an array with excluded names and if a branch is in the array, I skip the subfolders and files all the way out to the leaves

I didn't include any file parameters since I also found the requirements confusing also

27897




Option Explicit


Const sPathTop As String = "D:\Test"


Dim aryExclude As Variant
Dim o As Long
Dim FSO As Object


Sub Start()
aryExclude = Array("111CCC111", "333AAA", "333BBB")

o = 1


ActiveSheet.Columns(5).ClearContents ' testing purposes


Set FSO = CreateObject("Scripting.FileSystemObject")


Call GetFiles(FSO.GetFolder(sPathTop))


MsgBox "Done"
End Sub






Sub GetFiles(oPath As Object)
Dim oFolder As Object, oSubFolder As Object, oFile As Object


If Not IsExcluded(oPath) Then
ActiveSheet.Cells(o, 5).Value = oPath.path
o = o + 1

For Each oFile In oPath.Files
ActiveSheet.Cells(o, 5).Value = oFile.path
o = o + 1
Next

For Each oSubFolder In oPath.SubFolders
Call GetFiles(oSubFolder)
Next

End If


End Sub


Private Function IsExcluded(p As Object) As Boolean
Dim i As Long
IsExcluded = True

For i = LBound(aryExclude) To UBound(aryExclude)
If UCase(p.Name) = UCase(aryExclude(i)) Then Exit Function
Next i

IsExcluded = False
End Function

[anmac1789]

I don't think its that complicated to understand, seeing post #16 is more helpful. it's very close to what you are doing with the exception that instead of having the directory list already listed in the cells, it is excluding multiple subfolder paths (while also listing all files and folders recursively) during when my main code is run.. (from post #1). And also this only works for folder names, not folder paths

[Paul_Hossler]

I don't think its that complicated to understand, seeing post #16 is more helpful. it's very close to what you are doing with the exception that instead of having the directory list already listed in the cells, it is excluding multiple subfolder paths (while also listing all files and folders recursively) during when my main code is run.. (from post #1). And also this only works for folder names, not folder paths

All easily changed

[anmac1789]

All easily changed
pardon me ?

[Paul_Hossler]

pardon me ?




the exception that instead of having the directory list already listed in the cells, it is excluding multiple subfolder paths (while also listing all files and folders recursively) during when my main code is run.. (from post #1). And also this only works for folder names, not folder paths

The noted exceptions (directory path in cells, folder names instead of paths, listing the files' parameters) can easily be added to the macro in #31




it is excluding multiple subfolder paths (while also listing all files and folders recursively)

If a subfolder is exclused, I don't see my macro listing anything within that subfolder. Can you provide an example?



I want this functionality to loop for all files and all subfolders within the specified exclude folder. Thank you & I hope it makes some sense

As others have pointed out, the requirements are less than clear. If a folder is excluded, then why loop?

[anmac1789]

The noted exceptions (directory path in cells, folder names instead of paths, listing the files' parameters) can easily be added to the macro in #31
I have tried to change the subfolder names into subfolder paths and still the paths are not being excluded. Your code works well for excluding subfolder names (but what if I have the same subfolder name in a different path, so all occurances of that folder path would be excluded, which is not the goal). therefore, specific subfolder paths are what is needed

[Paul_Hossler]

I changed one line in IsExcluded

To test I used full paths in my Exclude array, and added a 4th level subfolder 111CCC111 to other 2nd and 3rd folders in my test tree

27908




Option Explicit


Const sPathTop As String = "D:\Test"


Dim aryExclude As Variant
Dim o As Long
Dim FSO As Object


Sub Start()
aryExclude = Array( _
"D:\Test\111\111CCC\111CCC111", _
"D:\Test\333\333AAA", _
"D:\Test\333\333BBB" _
)

o = 1


ActiveSheet.Columns(5).Clear ' testing purposes


Set FSO = CreateObject("Scripting.FileSystemObject")


Call GetFiles(FSO.GetFolder(sPathTop))


MsgBox "Done"
End Sub






Sub GetFiles(oPath As Object)
Dim oFolder As Object, oSubFolder As Object, oFile As Object


If Not IsExcluded(oPath) Then
ActiveSheet.Cells(o, 5).Value = oPath.path
o = o + 1

For Each oFile In oPath.Files
ActiveSheet.Cells(o, 5).Value = oFile.path
o = o + 1
Next

For Each oSubFolder In oPath.SubFolders
Call GetFiles(oSubFolder)
Next

End If


End Sub


Private Function IsExcluded(p As Object) As Boolean
Dim i As Long

IsExcluded = True

For i = LBound(aryExclude) To UBound(aryExclude)
If UCase(p.path) = UCase(aryExclude(i)) Then Exit Function ' <<<<<<<
Next i

IsExcluded = False
End Function

[anmac1789]

Hello, so it looks like this code does exactly what I want it to with me modifying something. I changed the parent folder code to list all files/folders within the parent directory (minus one less delimiter) you will see in the code (column B). The line of code that does that is:



Replace(Left(oPath.path, (Len(oPath.path) - Len(oPath.Name) - 1)), "\\?\", "") 'parent folder for subfolders

Replace(Left(oFile.path, (Len(oFile.path) - Len(oFile.Name) - 1)), "\\?\", "") 'parent folder for files


it seems like that even the excluding folder paths work for deeper nested subfolders as well which is precisely what I was after for SOO LONG. my final code is:


Option Explicit

Const sPathTop As String = ""


Dim aryExclude As Variant
Dim o As Long
Dim FSO As Object


Sub Start()
aryExclude = Array("C:\test with spaces\subfolder 2", "C:\test with spaces\subfolder 1") 'place excluded folder paths here!!

o = 2


ActiveSheet.Columns("A:H").Clear ' testing purposes


Set FSO = CreateObject("Scripting.FileSystemObject")


Call GetFiles(FSO.GetFolder("C:\test with spaces")) 'attach "\\?\" at the beginning for long folder path names! ex..'GetFiles("\\?\INSERT..." 'can also list multiple "Call GetFiles("\\?\[insert new folder path here]")" to list multiple folder paths all at once


End Sub


Sub GetFiles(oPath As Object)
Dim oFolder As Object, oSubFolder As Object, oFile As Object
Cells(1, 1).Value = "FILE/FOLDER PATH"
Cells(1, 1).Offset(0, 1).Value = "PARENT FOLDER"
Cells(1, 1).Offset(0, 2).Value = "FILE/FOLDER NAME"
Cells(1, 1).Offset(0, 3).Value = "FILE or FOLDER"
Cells(1, 1).Offset(0, 4).Value = "DATE CREATED"
Cells(1, 1).Offset(0, 5).Value = "DATE MODIFIED"
Cells(1, 1).Offset(0, 6).Value = "SIZE"
Cells(1, 1).Offset(0, 7).Value = "TYPE"


If Not IsExcluded(oPath) Then
ActiveSheet.Cells(o, 1).Value = Replace(oPath.path, "\\?\", "")
ActiveSheet.Cells(o, 2).Value = Replace(Left(oPath.path, (Len(oPath.path) - Len(oPath.Name) - 1)), "\\?\", "") 'parent folder for subfolders
ActiveSheet.Cells(o, 3).Value = oPath.Name
ActiveSheet.Cells(o, 4).Value = "folder"
ActiveSheet.Cells(o, 5).Value = oPath.datecreated
ActiveSheet.Cells(o, 6).Value = oPath.datelastmodified
o = o + 1

For Each oFile In oPath.Files
ActiveSheet.Cells(o, 1).Value = Replace(oFile.path, "\\?\", "")
ActiveSheet.Cells(o, 2).Value = Replace(Left(oFile.path, (Len(oFile.path) - Len(oFile.Name) - 1)), "\\?\", "") 'parent folder for files
ActiveSheet.Cells(o, 3).Value = oFile.Name
ActiveSheet.Cells(o, 4).Value = "file"
ActiveSheet.Cells(o, 5).Value = oFile.datecreated
ActiveSheet.Cells(o, 6).Value = oFile.datelastmodified
ActiveSheet.Cells(o, 7).Value = oFile.Size
ActiveSheet.Cells(o, 8).Value = oFile.Type
o = o + 1
Next

For Each oSubFolder In oPath.SubFolders
Call GetFiles(oSubFolder)
Next

With Range("E:F")
.NumberFormat = "dddd mmmm dd, yyyy H:mm:ss AM/PM" 'long file date and time
End With

End If

ActiveSheet.UsedRange.EntireColumn.AutoFit


End Sub


Private Function IsExcluded(p As Object) As Boolean
Dim i As Long

IsExcluded = True

For i = LBound(aryExclude) To UBound(aryExclude)
If UCase(p.path) = UCase(aryExclude(i)) Then Exit Function ' <<<<<<<
Next i

IsExcluded = False
End Function


I have attached a zip file which includes the files and folders that need to be placed on the C:\ drive for anyone looking to see & use my code for a complete solution.

It looks like there's no other functionality I need to add to this code. This code is basically what I was looking for since November 2020. This is what I wanted to help me see where so many duplicates are on my computer instead of opening up many folders all at once and sorting them manually.

Another program that I used is voidtool's program called 'everything' (voidtools (https://www.voidtools.com/)) but the shortcomings it has is that it cannot list folders and files together independently. A second program that I found just yesterday which does list them independently together is Jam software's program called 'ultrasearch' (UltraSearch (https://www.jam-software.com/ultrasearch_free)). The shortcoming of this is that the search syntax is basically really horrible and tedius and doesn't have much flexibility to search and find results that 'everything' has which is what I like much better. If I need additional functionality added then I have this code as a starting basis in this post as reference.

This is why I was looking for an excel solution and this is what I wanted so thank you to those who helped contributed code to this thread & took their time to respond to my annoying posts. I sincerely appreciate all your efforts!!!