Combine recursive listing with excluded code

[anmac1789]

This questions is an extension from this thread: http://www.vbaexpress.com/forum/showthread.php?68347-exclude-folder-paths-list/page2.

if you run this code with any chosen parent directory once, it lists all files and folders for that parent directory. Then, when you click button 1 again to list the same parent directory or a different parent directory, it adds to the previous list and continues on down to list files/folders for the new parent directory AFTER the 1st list. I've demonstrated that in the attached workbook using colour codes. The next thing is that the 2nd code has the excluded folder paths code. Now what I want to do is to combine both codes together into one code which has the recursive repeat list from the 1st code and the exclude folder paths code from the 2nd code...

This is the code in reference to the attached workbook:


Option Explicit
Sub SomeSub()
'ActiveSheet.Columns("A:H").ClearContents
Call GetFiles("\\?\C:\test one") '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


'Range("A1") = "FILE/FOLDER PATH"
'Range("A1").Offset(0, 1) = "parent folder"
'Range("A1").Offset(0, 2) = "FILE/FOLDER NAME"
'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(Left(folder, (Len(folder) - Len(folder.Name) - 1)), "\\?\", "")
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) = "SUB 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

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

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


'ActiveSheet.UsedRange.EntireColumn.AutoFit


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


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


This is the second code which has excluded folder paths:

Option Explicit

Const sPathTop As String = ""


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


Sub Start()
aryExclude = Array("C:\test one\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 one")) '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 tried myself to understand subroutine arguements from the 1st code and 2nd code and tried to comebine them together into one code but I always receive a compile error. it seems like the problem is stemming from not understanding the difference between
Sub GetFiles(oPath As Object) and
Sub GetFiles(ByVal path As String) and

For Each SubFolder In folder.Subfolders
GetFiles (SubFolder.path)
Next
I hope someone can shed some light onto how I can combine the two codes together....

[Paul_Hossler]

I'm still not sure about some things that you're doing, but I still had my test folder tree so I took another look

This seems to work

IMHO it's better to modularize and have subs/functions that can be used and which only perform one function

So there's Init(), ListInfor(), etc.

You can make the code more elegant, but I sort of brute forced a lot of it.

I also deleted your "\\?" since I didn't need it



Option Explicit


Const sPathTop As String = "D:\Test"


Dim aryExclude As Variant
Dim rowOut As Long
Dim oFSO As Object
Dim wsOut As Worksheet


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

Init


Call GetFiles(oFSO.GetFolder(sPathTop))

wsOut.Columns(5).NumberFormat = "m/dd/yyyy"
wsOut.Columns(6).NumberFormat = "m/dd/yyyy"
wsOut.Columns(7).NumberFormat = "#,##0,.0 ""KB"""


MsgBox "Done"
End Sub






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


If IsExcluded(oPath) Then Exit Sub ' stops recursion

Call ListInfo(oPath, "Folder")

For Each oFile In oPath.Files
Call ListInfo(oFile, "File")
Next

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


'========================================================================== ==
Private Sub Init()
Set wsOut = Worksheets("Files")

rowOut = 1

With wsOut
.Cells(rowOut, 1).Value = "FILE/FOLDER PATH"
.Cells(rowOut, 2).Value = "PARENT FOLDER"
.Cells(rowOut, 3).Value = "FILE/FOLDER NAME"
.Cells(rowOut, 4).Value = "FILE or FOLDER"
.Cells(rowOut, 5).Value = "DATE CREATED"
.Cells(rowOut, 6).Value = "DATE MODIFIED"
.Cells(rowOut, 7).Value = "SIZE"
.Cells(rowOut, 8).Value = "TYPE"
End With


rowOut = rowOut + 1


Set oFSO = CreateObject("Scripting.FileSystemObject")
End Sub


' IFolder object
' Attributes, DateCreated, DateLastAccessed, DateLastModified, Drive,
' Files, IsRootFolder, Name, ParentFolder (IFolder), Path,
' ShortName, ShortPath, Size, SubFolders, Type


' iFile object
' Attributes, DateCreated, DateLastAccessed, DateLastModified, Drive (IDrive),
' Name, ParentFolder (IFolder), Path, ShortName, ShortPath, Size, Type
' Attributes


Private Sub ListInfo(oFile As Object, sType As String)
With oFile
wsOut.Cells(rowOut, 1).Value = .path
wsOut.Cells(rowOut, 2).Value = .ParentFolder.path
wsOut.Cells(rowOut, 3).Value = .Name
wsOut.Cells(rowOut, 4).Value = sType
wsOut.Cells(rowOut, 5).Value = .DateCreated
wsOut.Cells(rowOut, 6).Value = .DateLastModified
wsOut.Cells(rowOut, 7).Value = .Size
wsOut.Cells(rowOut, 8).Value = .Type
End With

rowOut = rowOut + 1
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]

Also, this code doesn't continue to the list after the 1st search it clears the worksheet and starts a new search instead of continuing down. From what I've gathered, using
Range("A" & Rows.Count).End(xlUp).Offset(0, 1) does the trick of continuing with the list downwards. But I dont know if this is making the code more complicated or making it run slower..


IMHO it's better to modularize and have subs/functions that can be used and which only perform one function
Do you mean to put subroutine codes for files, subfolders, parent folder in it's own module?


You can make the code more elegant, but I sort of brute forced a lot of it. I also did no final formatting (mm/dd/yyyy for dates and n,nnn KB for sizes)
To make this code much simpler and easier/faster to run do you mean to use arrays ? Also, I can change the date and time format to how I want later on after it's searched through all files/folders...unless there's a faster way of declaring the formatting at the beginning of the code so it doesn't have to wait until after the code has completed...

How come in this code:

Private Sub ListFolder(oFolder As Object) With oFolder
wsOut.Cells(rowOut, 1).Value = .path
wsOut.Cells(rowOut, 2).Value = .ParentFolder.path
wsOut.Cells(rowOut, 3).Value = .Name
wsOut.Cells(rowOut, 4).Value = "Folder"
wsOut.Cells(rowOut, 5).Value = .DateCreated
wsOut.Cells(rowOut, 6).Value = .DateLastModified
wsOut.Cells(rowOut, 7).Value = .Size
wsOut.Cells(rowOut, 8).Value = .Type
End With

rowOut = rowOut + 1
End Sub

the line with
wsOut.Cells(rowOut, 4).Value = "Folder" doesn't show the column with "folder" but instead it shows "subfolder"

I just found out that leaving this code out makes no difference in listing the subfolder path and as well as the parent folder paths! that's a bit strange...


Private Sub ListFolder(oFolder As Object)
With oFolder
wsOut.Cells(rowOut, 1).Value = .path
wsOut.Cells(rowOut, 2).Value = .ParentFolder.path
wsOut.Cells(rowOut, 3).Value = .Name
wsOut.Cells(rowOut, 4).Value = "Folder"
wsOut.Cells(rowOut, 5).Value = .DateCreated
wsOut.Cells(rowOut, 6).Value = .DateLastModified
wsOut.Cells(rowOut, 7).Value = .Size
wsOut.Cells(rowOut, 8).Value = .Type
End With

rowOut = rowOut + 1
End Sub

[Paul_Hossler]

I revised my macro a few minutes after I posted it last night at 10:26 since I wanted to simplify some things. Are you looking at the revised version?


1. Changed Init().
Using rowOut (a Long) is faster than tracing several objects and methods

2. No, instead of having basically the same 8 lines to put data onto the worksheet, a separate module (ListInfo) with calling parameters will put it in one place. Also makes the overall macro smaller and maintenance is usually easier

3. Arrays would make part of it faster, but IMHO the bulk of the time is spent getting information about files/folders the complexity wasn't worth it

4. A 'subfolder' is still a 'folder'. Something like oFolder.Subfolders is a collection of Folder objects in oFolder, but For Each returns a Folder object
That's why ListFolder was never used and I replaced ListFile and ListSubfolder with just ListInfo, since they both did 90% the same thing, the old difference being what you call it in cell 4

5. You could format (date, size) each line as you go, but I thinks it's faster to just do the whole column at once


The main recursive code is below, so if you want it to say "Subfolder", change the marked line



Call ListInfo(oPath, "Folder") ' <<<<<<<<<<<<<<<

For Each oFile In oPath.Files
Call ListInfo(oFile, "File")
Next







Option Explicit


Const sPathTop As String = "D:\Test"


Dim aryExclude As Variant
Dim rowOut As Long
Dim oFSO As Object
Dim wsOut As Worksheet


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

Init


Call GetFiles(oFSO.GetFolder(sPathTop))

wsOut.Columns(5).NumberFormat = "m/dd/yyyy"
wsOut.Columns(6).NumberFormat = "m/dd/yyyy"
wsOut.Columns(7).NumberFormat = "#,##0,.0 ""KB"""


MsgBox "Done"
End Sub






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


If IsExcluded(oPath) Then Exit Sub ' stops recursion

Call ListInfo(oPath, "Folder")

For Each oFile In oPath.Files
Call ListInfo(oFile, "File")
Next

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


'========================================================================== ==
Private Sub Init()
Set wsOut = Worksheets("Files")

With wsOut

rowOut = .Cells(.Rows.Count, 1).End(xlUp).Row

If rowOut = 1 Then ' blank sheet
.Cells(rowOut, 1).Value = "FILE/FOLDER PATH"
.Cells(rowOut, 2).Value = "PARENT FOLDER"
.Cells(rowOut, 3).Value = "FILE/FOLDER NAME"
.Cells(rowOut, 4).Value = "FILE or FOLDER"
.Cells(rowOut, 5).Value = "DATE CREATED"
.Cells(rowOut, 6).Value = "DATE MODIFIED"
.Cells(rowOut, 7).Value = "SIZE"
.Cells(rowOut, 8).Value = "TYPE"

rowOut = rowOut + 1
End If
End With


Set oFSO = CreateObject("Scripting.FileSystemObject")
End Sub


' IFolder object
' Attributes, DateCreated, DateLastAccessed, DateLastModified, Drive,
' Files, IsRootFolder, Name, ParentFolder (IFolder), Path,
' ShortName, ShortPath, Size, SubFolders, Type


' iFile object
' Attributes, DateCreated, DateLastAccessed, DateLastModified, Drive (IDrive),
' Name, ParentFolder (IFolder), Path, ShortName, ShortPath, Size, Type
' Attributes


Private Sub ListInfo(oFile As Object, sType As String)
With oFile
wsOut.Cells(rowOut, 1).Value = .path
wsOut.Cells(rowOut, 2).Value = .ParentFolder.path
wsOut.Cells(rowOut, 3).Value = .Name
wsOut.Cells(rowOut, 4).Value = sType
wsOut.Cells(rowOut, 5).Value = .DateCreated
wsOut.Cells(rowOut, 6).Value = .DateLastModified
wsOut.Cells(rowOut, 7).Value = .Size
wsOut.Cells(rowOut, 8).Value = .Type
End With

rowOut = rowOut + 1
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]

I see, so the code is a bit shorter now instead of using
Range("A" & Rows.Count).End(xlUp).Offset(0, 1)= by using
wsOut.Cells(rowOut, 1).Value =

Also, the reason why I used "\\?" prefix is because for long folder path names exceeding 260 or 255 characters because I have one folder which is longer than 255 or 260 characters


I revised my macro a few minutes after I posted it last night at 10:26 since I wanted to simplify some things. Are you looking at the revised version? I saw that some code was changed yes thats why did a line by line comparison of my code with your code and it seems like not much changed.


4. A 'subfolder' is still a 'folder'. Something like oFolder.Subfolders is a collection of Folder objects in oFolder, but For Each returns a Folder object
That's why ListFolder was never used and I replaced ListFile and ListSubfolder with just ListInfo, since they both did 90% the same thing, the old difference being what you call it in cell 4 Also, I see by removing ListFile and ListSubfolder, the 4th column with the header "FILE or FOLDER" returns only "file" or "folder"....but if I also wanted to say "parent folder" for top level path such as the directory paths used in
Call GetFiles(oFSO.GetFolder("PARENT DIRECTORY")) and "subfolder" for subfolders after the parent directory and "files" for regular files..Would I still need to make a separate attribute list one for parent directory and another for subfolders and files?

[Paul_Hossler]

I'm not understanding

Every file and every folder has a parent directory

If a folder (#1) contains subfolders (#A, #B, #C) , than #A is also a folder, and has #1 as their parent

In my little test the folder "D:\Test" the the top of the search tree

Are you saying that in the sample below from my test, that you want the BLUE to say "Parent Folder" and the GREEN to say "Subfolder"?


27945

A second run on a different top level folder (e.g. F:\Test2) would be / could be labeled as "Parent Folder"

[anmac1789]

Are you saying that in the sample below from my test, that you want the BLUE to say "Parent Folder" and the GREEN to say "Subfolder"?
Yes I was looking for the blue folder to say "parent folder" or any string name like "parent" "top level" etc..and the green to say "subfolder" and the files just regular plain old "file"


A second run on a different top level folder (e.g. F:\Test2) would be / could be labeled as "Parent Folder" yes the same labels to be repeated for a different (or repeat of the same) parent folder etc..

Also, how come I cannot use "\\?" in the prefix of
Call GetFiles(oFSO.GetFolder("\\?\PARENT DIRECTORY")) because I was able to do that in
Call GetFiles("\\?\C:\test one") for shorter file path names from this code:


Option ExplicitSub SomeSub()
'ActiveSheet.Columns("A:H").ClearContents
Call GetFiles("\\?\C:\test one") '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


'Range("A1") = "FILE/FOLDER PATH"
'Range("A1").Offset(0, 1) = "parent folder"
'Range("A1").Offset(0, 2) = "FILE/FOLDER NAME"
'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(Left(folder, (Len(folder) - Len(folder.Name) - 1)), "\\?\", "")
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) = "SUB 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


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


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


'ActiveSheet.UsedRange.EntireColumn.AutoFit


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

End Sub

Also, using the prefix gives the error: "Run-time error 76: Path not found" when i click debug it highlights this line
wsOut.Cells(rowOut, 2).Value = .ParentFolder.path
Does this line
Sub GetFiles(ByVal path As String) have anything to do with accepting the longer path prefix or no?

[Paul_Hossler]

Also, how come I cannot use "\\?" in the prefix of
Call GetFiles(oFSO.GetFolder("\\?\PARENT DIRECTORY"))

Probably because there is not a folder on disk called PARENT DIRECTORY.

.ParentFolder is a property of the File and the Folder objects

I don't know why the \\?\ prefix causes an error


27947



Try this as a workaround


Option Explicit

Const sPathTop As String = "\\?\D:\Test"


Dim aryExclude As Variant
Dim rowOut As Long
Dim oFSO As Object
Dim wsOut As Worksheet
Dim sParentFolder As String


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

Init


rowStart = rowOut
sParentFolder = RemovePrefix(sPathTop)


Call GetFiles(oFSO.GetFolder(sPathTop))

wsOut.Cells(rowStart, 4).Value = "Parent Folder"

wsOut.Columns(5).NumberFormat = "m/dd/yyyy"
wsOut.Columns(6).NumberFormat = "m/dd/yyyy"
wsOut.Columns(7).NumberFormat = "#,##0,.0 ""KB"""


MsgBox "Done"
End Sub






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


If IsExcluded(oPath) Then Exit Sub ' stops recursion

sParentFolder = IIf(Left(oPath.path, 4) = "\\?\", Right(oPath.path, Len(oPath.path) - 4), oPath.path)

Call ListInfo(oPath, "Subfolder")

For Each oFile In oPath.Files
Call ListInfo(oFile, "File")
Next

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


'========================================================================== ==
Private Sub Init()
Set wsOut = Worksheets("Files")

With wsOut

rowOut = .Cells(.Rows.Count, 1).End(xlUp).Row

If rowOut = 1 Then ' blank sheet
.Cells(rowOut, 1).Value = "FILE/FOLDER PATH"
.Cells(rowOut, 2).Value = "PARENT FOLDER"
.Cells(rowOut, 3).Value = "FILE/FOLDER NAME"
.Cells(rowOut, 4).Value = "FILE or FOLDER"
.Cells(rowOut, 5).Value = "DATE CREATED"
.Cells(rowOut, 6).Value = "DATE MODIFIED"
.Cells(rowOut, 7).Value = "SIZE"
.Cells(rowOut, 8).Value = "TYPE"

rowOut = rowOut + 1
End If
End With


Set oFSO = CreateObject("Scripting.FileSystemObject")
End Sub


' IFolder object
' Attributes, DateCreated, DateLastAccessed, DateLastModified, Drive,
' Files, IsRootFolder, Name, ParentFolder (IFolder), Path,
' ShortName, ShortPath, Size, SubFolders, Type


' iFile object
' Attributes, DateCreated, DateLastAccessed, DateLastModified, Drive (IDrive),
' Name, ParentFolder (IFolder), Path, ShortName, ShortPath, Size, Type
' Attributes


Private Sub ListInfo(oFolderFile As Object, sType As String)
With oFolderFile
wsOut.Cells(rowOut, 1).Value = RemovePrefix(.path)
' wsOut.Cells(rowOut, 2).Value = .ParentFolder.path
wsOut.Cells(rowOut, 2).Value = sParentFolder
wsOut.Cells(rowOut, 3).Value = .Name
wsOut.Cells(rowOut, 4).Value = sType
wsOut.Cells(rowOut, 5).Value = .DateCreated
wsOut.Cells(rowOut, 6).Value = .DateLastModified
wsOut.Cells(rowOut, 7).Value = .Size
wsOut.Cells(rowOut, 8).Value = .Type
End With

rowOut = rowOut + 1
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


Private Function RemovePrefix(s As String) As String
RemovePrefix = IIf(Left(s, 4) = "\\?\", Right(s, Len(s) - 4), s)
End Function

[anmac1789]

Probably because there is not a folder on disk called PARENT DIRECTORY. I meant that has a generic placeholder for any parent directory of your choosing


I don't know why the \\?\ prefix causes an errorHow come I cant use something simple like this
Replace(folder path, "\\?\", "")

Also, from post #4, using this code, it overwrites the last line of the folder/file before pressing a button to continue a 2nd search

Option Explicit

Const sPathTop As String = "D:\Test"


Dim aryExclude As Variant
Dim rowOut As Long
Dim oFSO As Object
Dim wsOut As Worksheet


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

Init


Call GetFiles(oFSO.GetFolder(sPathTop))

wsOut.Columns(5).NumberFormat = "m/dd/yyyy"
wsOut.Columns(6).NumberFormat = "m/dd/yyyy"
wsOut.Columns(7).NumberFormat = "#,##0,.0 ""KB"""


MsgBox "Done"
End Sub






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


If IsExcluded(oPath) Then Exit Sub ' stops recursion

Call ListInfo(oPath, "Folder")

For Each oFile In oPath.Files
Call ListInfo(oFile, "File")
Next

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


'========================================================================== ==
Private Sub Init()
Set wsOut = Worksheets("Files")

With wsOut

rowOut = .Cells(.Rows.Count, 1).End(xlUp).Row

If rowOut = 1 Then ' blank sheet
.Cells(rowOut, 1).Value = "FILE/FOLDER PATH"
.Cells(rowOut, 2).Value = "PARENT FOLDER"
.Cells(rowOut, 3).Value = "FILE/FOLDER NAME"
.Cells(rowOut, 4).Value = "FILE or FOLDER"
.Cells(rowOut, 5).Value = "DATE CREATED"
.Cells(rowOut, 6).Value = "DATE MODIFIED"
.Cells(rowOut, 7).Value = "SIZE"
.Cells(rowOut, 8).Value = "TYPE"

rowOut = rowOut + 1
End If
End With


Set oFSO = CreateObject("Scripting.FileSystemObject")
End Sub


' IFolder object
' Attributes, DateCreated, DateLastAccessed, DateLastModified, Drive,
' Files, IsRootFolder, Name, ParentFolder (IFolder), Path,
' ShortName, ShortPath, Size, SubFolders, Type


' iFile object
' Attributes, DateCreated, DateLastAccessed, DateLastModified, Drive (IDrive),
' Name, ParentFolder (IFolder), Path, ShortName, ShortPath, Size, Type
' Attributes


Private Sub ListInfo(oFile As Object, sType As String)
With oFile
wsOut.Cells(rowOut, 1).Value = .path
wsOut.Cells(rowOut, 2).Value = .ParentFolder.path
wsOut.Cells(rowOut, 3).Value = .Name
wsOut.Cells(rowOut, 4).Value = sType
wsOut.Cells(rowOut, 5).Value = .DateCreated
wsOut.Cells(rowOut, 6).Value = .DateLastModified
wsOut.Cells(rowOut, 7).Value = .Size
wsOut.Cells(rowOut, 8).Value = .Type
End With

rowOut = rowOut + 1
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

So for example, suppose there's 10 files, 7 folders for a total of =17 objects in a parent directory. When searching the first time, it produces 17 objects correctly. Searching again recursively of the same parent directory overwrites the last line from the 1st search and produces 17 more objects. In total, only 33 objects were listed instead of 34

Sorry, I know it seems im picking out every detail but it seems like we're close

[Paul_Hossler]

New Init() in #8



Private Sub Init()
Set wsOut = Worksheets("Files")

With wsOut

rowOut = .Cells(.Rows.Count, 1).End(xlUp).Row

If rowOut = 1 Then ' blank sheet
.Cells(rowOut, 1).Value = "FILE/FOLDER PATH"
.Cells(rowOut, 2).Value = "PARENT FOLDER"
.Cells(rowOut, 3).Value = "FILE/FOLDER NAME"
.Cells(rowOut, 4).Value = "FILE or FOLDER"
.Cells(rowOut, 5).Value = "DATE CREATED"
.Cells(rowOut, 6).Value = "DATE MODIFIED"
.Cells(rowOut, 7).Value = "SIZE"
.Cells(rowOut, 8).Value = "TYPE"
End If

rowOut = rowOut + 1

End With


Set oFSO = CreateObject("Scripting.FileSystemObject")
End Sub

[anmac1789]

okay so that makes sense, it is listing the correct number of files/folders. Oh shoot! I just thought of something right now... I don't know if I should make a new thread or continue posting here...it is related somewhat to this topic...

let's say I have two subfolders within a parent folder and I wanted to remove or add a subfolder to the search list after the 1st search, if I search again it's going to duplicate some results like the parent directory or other subfolders in the 2nd search result (in other words, going to make a duplicate list). Basically this means that whenever a new subfolder/parent directory is added/removed, is there a way to check for duplicated folder paths and remove them (let's say after the 2nd search, 3rd search etc..etc..)? so as there is only one folder/file path and it's attributes in the search list?

..this sounds complicated...so I should we solve this with VBA code or instead use built-in excel functions?

so it looks like, I have found one code that does what I want it to, except I don't know to erase the rows. I found this from another website and I don't know if this code can be simplified further:

Sub sbFindDuplicatesInColumn_C()'Declaring the lastRow variable as Long to store the last row value in the Column1
Dim lastRow As Long


'matchFoundIndex is to store the match index values of the given value
Dim matchFoundIndex As Long


'iCntr is to loop through all the records in the column 1 using For loop
Dim iCntr As Long


'Finding the last row in the Column 1
lastRow = Range("A65000").End(xlUp).Row


'looping through the column1
For iCntr = 1 To lastRow
'checking if the cell is having any item, skipping if it is blank.
If Cells(iCntr, 1) <> "" Then
'getting match index number for the value of the cell
matchFoundIndex = WorksheetFunction.Match(Cells(iCntr, 1).Value, Range("A1:A" & lastRow), 0)
'if the match index is not equals to current row number, then it is a duplicate value
If iCntr <> matchFoundIndex Then
'Printing the label in the column B
Cells(iCntr, 1).Interior.Color = RGB(255, 12, 0)
Cells(iCntr, 9) = "duplicate"
End If
End If
Next
End Sub

[Paul_Hossler]

Added new Sub to remove duplicate and some cleanup

It looks at just Parent values for the lines that were just added (another run) and if the Parent is in the previous data, marks and deletes the just added row




'look at newly added lines (not in rPrev) and if PARENT FOLDER is in rPrev delete from newly added
Private Sub RemoveDups()
Dim rowNew As Long

For rowNew = wsOut.Cells(1, 1).CurrentRegion.Rows.Count To rPrev.Rows.Count + 1 Step -1
If Application.WorksheetFunction.CountIf(rPrev.Columns(colParent), wsOut.Cells(rowNew, colParent).Value) > 0 Then
wsOut.Cells(rowNew, colParent).Value = True
End If
Next rowNew

On Error Resume Next
wsOut.Columns(colParent).SpecialCells(xlCellTypeConstants, xlLogical).EntireRow.Delete
On Error GoTo 0


End Sub

[anmac1789]

well, I know your trying your best here, but if I had several nested subfolders with the same names then if just the parent values were being looked at then additional folder paths would be removed that's not intended to.

What I mean here is that, folder/file paths uniquely identify each parent folder, each file, each subfolder...so even if they have the same "end" name they would be at a different level...hence which is why I figure out right now that folder/file paths is the best way to remove duplicates.

What I found over the internet is to mark duplicates with a colour and check if they are actual duplicate/repeated values due to multiple searches and then delete those entries.

To expand on this, I have made 3 buttons, 1 button to search everything, 2nd button to find and look for duplicates, third button to remove those highlighted/marked duplicates. However, I see some of your coding looks cleaned up with a bit with constants so I want to use that

I know it is a little complicated to understand but I think i found a way right now...here is the additional code:

Option Explicit

Sub sbFindDuplicatesInColumn_C()
'Declaring the lastRow variable as Long to store the last row value in the Column1
Dim lastRow As Long


'matchFoundIndex is to store the match index values of the given value
Dim matchFoundIndex As Long


'iCntr is to loop through all the records in the column 1 using For loop
Dim iCntr As Long


'Finding the last row in the Column 1
lastRow = Range("A65000").End(xlUp).Row


'looping through the column1
For iCntr = 1 To lastRow
'checking if the cell is having any item, skipping if it is blank.
If Cells(iCntr, 1) <> "" Then
'getting match index number for the value of the cell
matchFoundIndex = WorksheetFunction.Match(Cells(iCntr, 1), Range("A1:A" & lastRow), 0)
'if the match index is not equals to current row number, then it is a duplicate value
If iCntr <> matchFoundIndex Then
'Printing the label in the column B
'Cells(iCntr, 1).EntireRow.Delete
Cells(iCntr, 1).Interior.Color = RGB(255, 20, 0)
'Cells(iCntr, 9) = "duplicate"
End If
End If
Next
End Sub




'Sub DeleteRowswithSpecificValue()
'Dim i As Variant
'For i = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
'If Cells(i, 1).Value = "c:\test with spaces" Then
'Cells(i, 1).Interior.Color = RGB(255, 0, 0)
'Cells(i, 3).Interior.Color = RGB(0, 255, 0)
'Cells(i, 2).Interior.Color = RGB(0, 255, 255)
'End If
'Next i
'End Sub


Sub Deleteit()
Dim i As Variant
For i = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
If Cells(i, 1).Interior.Color = RGB(255, 20, 0) Then
Cells(i, 1).EntireRow.Delete
'Cells(i, 1).Interior.Color = RGB(0, 255, 0)
End If
Next i
End Sub
I am wondering if this could be cleaned up or not..and also I have placed this code into a new module (module 2)

[Paul_Hossler]

Confused as to what it is you want

I ran on my test folder tree and excluded "\\?\D:\Test\111" and got the green, rows 2 - 78, with no D:\Test\111, but did include d:\Test\222

Ran again and excluded "\\?\D:\Test\222" and got the yellow, rows 79 - 105. Anything that was not excluded in run 2 (everything except D:\Test\222) but was already included in run 1 was removed

The new items from the second run were d:\Test\111, original excluded from run 1

27951

[anmac1789]

Confused as to what it is you wantOh i see what you are doing, basically after every new run, in the new run whatever files/folders that are included in the previous run, are removed from the newer run..so basically with each new run it's checking back to see if the file/folder exists or not..if not its keeping the file/folder if it does exists it deletes it from the new run. Am I correct in this reasoning?

Basically, my code does exactly what your code does except it lists everything first (excluding defined folder paths), selects or highlights duplicate folder paths and then there's a button to remove them...the end result is similar to your code above

Finally, I think this is what the function does:

D:\Test\111
D:\Test\222
D:\Test\333

run 1: exclude: D:\Test\111, include: D:\Test\222, D:\Test\333
run 2: exclude: D:\Test\222, include: D:\Test\111, D:\Test\333 (D:\Test\333 removed, because it is already listed from run 1)

[Paul_Hossler]

Oh i see what you are doing, basically after every new run, in the new run whatever files/folders that are included in the previous run, are removed from the newer run..so basically with each new run it's checking back to see if the file/folder exists or not..if not its keeping the file/folder if it does exists it deletes it from the new run. Am I correct in this reasoning?

Yes



Basically, my code does exactly what your code does except it lists everything first (excluding defined folder paths), selects or highlights duplicate folder paths and then there's a button to remove them...the end result is similar to your code above


I didn't do it that way since if there were 3-4 runs, and THEN the manual de-dup it would get confusing
Right before each run, the macro knows what the starting data is, and can tell the difference between that 'old' data and the 'new' data being added in the second run to eliminate overlaps from the new data




Finally, I think this is what the function does:

D:\Test\111
D:\Test\222
D:\Test\333

run 1: exclude: D:\Test\111, include: D:\Test\222, D:\Test\333
run 2: exclude: D:\Test\222, include: D:\Test\111, D:\Test\333 (D:\Test\333 removed, because it is already listed from run 1)


Yes

Before:

D:\Test\111
D:\Test\222
D:\Test\333

Run 1: exclude D:\Test\111 from Before

D:\Test\222
D:\Test\333

Run 2: exclude D:\Test\222 from Before

D:\Test\111
D:\Test\333


Result:

D:\Test\222 (from run 1)
D:\Test\333 (from run 1)
D:\Test\111 (from run 2 since 333 was already there)

[anmac1789]

OKay got it..have you tried looking at this code here?


Option Explicit
Sub sbFindDuplicatesInColumn_C()
'Declaring the lastRow variable as Long to store the last row value in the Column1
Dim lastRow As Long


'matchFoundIndex is to store the match index values of the given value
Dim matchFoundIndex As Long


'iCntr is to loop through all the records in the column 1 using For loop
Dim iCntr As Long


'Finding the last row in the Column 1
lastRow = Range("A65000").End(xlUp).Row


'looping through the column1
For iCntr = 1 To lastRow
'checking if the cell is having any item, skipping if it is blank.
If Cells(iCntr, 1) <> "" Then
'getting match index number for the value of the cell
matchFoundIndex = WorksheetFunction.Match(Cells(iCntr, 1), Range("A1:A" & lastRow), 0)
'if the match index is not equals to current row number, then it is a duplicate value
If iCntr <> matchFoundIndex Then
'Printing the label in the column B
'Cells(iCntr, 1).EntireRow.Delete
Cells(iCntr, 1).Interior.Color = RGB(255, 20, 0)
'Cells(iCntr, 9) = "duplicate"
End If
End If
Next
End Sub




'Sub DeleteRowswithSpecificValue()
'Dim i As Variant
'For i = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
'If Cells(i, 1).Value = "c:\test with spaces" Then
'Cells(i, 1).Interior.Color = RGB(255, 0, 0)
'Cells(i, 3).Interior.Color = RGB(0, 255, 0)
'Cells(i, 2).Interior.Color = RGB(0, 255, 255)
'End If
'Next i
'End Sub


Sub Deleteit()
Dim i As Variant
For i = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
If Cells(i, 1).Interior.Color = RGB(255, 20, 0) Then
Cells(i, 1).EntireRow.Delete
'Cells(i, 1).Interior.Color = RGB(0, 255, 0)
End If
Next i End Sub

isn't this much simpler ? let me demonstrate by giving you my workbook so u can take a look at it..

[Paul_Hossler]

isn't this much simpler ? let me demonstrate by giving you my workbook so u can take a look at it..

Well, it appears to work, which is always a good thing

I don't find it any simpler or particularly well written, but that doesn't mean that you can't use it

Things like Range("A65000") and just Cells(...) not tied to a specific worksheet (e.g. Worksheets("Data").Cells (1,2)) I find will cause trouble later or that the macro is old and written in Excel 2003 originally

Why would you want 3 steps instead of just 1?

[anmac1789]

Things like Range("A65000") and just Cells(...) not tied to a specific worksheet (e.g. Worksheets("Data").Cells (1,2)) I find will cause trouble later or that the macro is old and written in Excel 2003 originally
I got this originally from the internet, I am relatively new to excel vba but i've been using it for about a month now so I don't know too much about how to make the code simpler and more efficient. I see, what you are trying to say, for example if I wanted to expand even further on this code and have different functionalities in different worksheets so using "Worksheets("Data").Cells (1,2)" this notation will prevent problems from occuring..


Why would you want 3 steps instead of just 1?usually, I like to see how the code is made up when it's written simply which again ties to what I said above being new and all...I want to learn more about how your code is written it's just a lot of new things are written in it so I don't know what's going on..

[Paul_Hossler]

Well, a lot is just personal style, but there are some things I've learned by making mistakes and/or by seeing how others do it that make it a little more transportable and bullet-proof

For example, using my Init() sub

Instead of using Range("A65000") [probably Excel 2003] which will return wrong answer if more than 65000 rows, I prefer (again, personal choice/style) to use the built in Excel capabilities (#1 below). I've seen Range("A1000") used which will probably fail sooner or later. The #1 works in 2003 and with 1M+ rows, and in 64 bit Excel and if they ever have Excel with 100M+ rows it will still work. More transportable

Just using Cells() refers to the ActiveSheet, which might not be the worksheet that you intended (I've seen many hard to trace bugs because the macro was checking or writing to the wrong worksheet). By using wsOut.Cells(...) (#2 below) it's clear that you're using the cell that you think your are

The bracketing With / End With (#3) is just a way to keep the code more readable (again, personal opinion) since all of the <dot>Cells(...) within are clearly part of the wsOut object




Private Sub Init()
Set wsOut = Worksheets("Files")

With wsOut ' <<<<<<<<<<<<<<<<<<<<<<<<<<< #3

rowOut = .Cells(.Rows.Count, 1).End(xlUp).Row ' <<<<<<<<<<<<<<<<<<<<<<<<<<< #1

If rowOut = 1 Then ' blank sheet
.Cells(rowOut, 1).Value = "FILE/FOLDER PATH" ' <<<<<<<<<<<<<<<<<<<<<<<<<<< #2
.Cells(rowOut, 2).Value = "PARENT FOLDER"
.Cells(rowOut, 3).Value = "FILE/FOLDER NAME"
.Cells(rowOut, 4).Value = "FILE or FOLDER"
.Cells(rowOut, 5).Value = "DATE CREATED"
.Cells(rowOut, 6).Value = "DATE MODIFIED"
.Cells(rowOut, 7).Value = "SIZE"
.Cells(rowOut, 8).Value = "TYPE"

rowOut = rowOut + 1
End If
End With ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<< #3


Set oFSO = CreateObject("Scripting.FileSystemObject") End Sub

[anmac1789]

If you don't mind me asking, I've had a problem in the past that lets say u want multiple properties and methods for a given range for example and I didn't know how to use that effectively so basically I was stuck writing:

range("A1").end(xlup).offset(row,column).other methods.other properties and so on and so on using these periods and sometimes I would create new variables and add to the range and then before I knew it, i was so confused beyond my understanding...

So are you saying that with/end wich, we can add multiple properties and methods to an object?


Instead of using Range("A65000") [probably Excel 2003] which will return wrong answer if more than 65000 rows, I prefer (again, personal choice/style) to use the built in Excel capabilities (#1 below). I've seen Range("A1000") used which will probably fail sooner or later. The #1 works in 2003 and with 1M+ rows, and in 64 bit Excel and if they ever have Excel with 100M+ rows it will still work. More transportableSo you're saying that in #1 here
rowOut = .Cells(.Rows.Count, 1).End(xlUp).Row ' <<<<<<<<<<<<<<<<<<<<<<<<<<< #1this will accept more than 1 million rows if the data has more than 1 million rows intead of using Range("A65000")


Just using Cells() refers to the ActiveSheet, which might not be the worksheet that you intended (I've seen many hard to trace bugs because the macro was checking or writing to the wrong worksheet). By using wsOut.Cells(...) (#2 below) it's clear that you're using the cell that you think your are

in #2 here:
.Cells(rowOut, 1).Value = "FILE/FOLDER PATH" ' <<<<<<<<<<<<<<<<<<<<<<<<<<< #2wsOut.Cells(...) was not used here..so how does excel determine that we are using wsOut worksheet?


The bracketing With / End With (#3) is just a way to keep the code more readable (again, personal opinion) since all of the <dot>Cells(...) within are clearly part of the wsOut object
Readibility and simplicity is what I strive for so if the code can be written more simply then I'm all for it..if you know any tips and tricks how you organize your code in day to day tasks or in this forum I would like to learn about it..

[Paul_Hossler]

Didn't mean to sound confusing

1. I was saying that Excel and VBA have a rich set of properties in the object model, so I try to use them as much as I can

For example, the Worksheet object has a .Rows property that returns the rows on the worksheet as a Range object, and the .Rows.Count property on a Range object tells you how many rows are in the range

So instead of using a 'Magic Number' like 1000 (a magic number is a number that just shows up) in



N = Range("A1000").End(xlUp).Row + 1


to get the next blank row, I (again it's just personal style) like to use something like



N = Worksheets("Data").Cells(Worksheets("Data").Rows.Count,1) .End(xlUp).Offset(1,0).Row



So are you saying that with/end with, we can add multiple properties and methods to an object?

Not exactly. It saves having to repeatedly specify the object. The With + End With is sort of a shortcut to save typing (for me that means less chance of error) and to improve readability

The 'dot' prefix is used to 'tie' something to it's parent so just using <dot>Cells(...) the 'With object is the parent



With Worksheets("Data")
N = .Cells(.Rows.Count,1) .End(xlUp).Offset(1,0).Row
.Cells(N,1).value = "Yes"
.Cells(N,2).Resize(1,10).Interior.Color = vbRed

.Cells(1,1).CurrentRegion.EntireColumn.Autofit
End With


No Magic Numbers and will work regardless of the number of WS rows in the version of Excel being used

2. I think it's very important to explicitly refer to objects

Say there are two sheets, "Data" which has 100 rows used and "SomethingElse which has 500 rows used

Using the bit of code just above, doesn't matter if Data is the ActiveSheet or if "SomethingElse" is the Activesheet, I'll get "Yes' and 10 red cells in row 101


If I were less explicit (below) and just ASSUMED that Data was always going to be the Activesheet when the macro was run, it would go to which ever the Activesheet really was (you know you can never trust users to leave things alone)



N = Cells(.Rows.Count,1) .End(xlUp).Offset(1,0).Row
Cells(N,1).value = "Yes"
Cells(N,2).Resize(1,10).Interior.Color = vbRed


Using the bit of code just above, I could get "Yes' and 10 red cells in row 101 of "Data" or I could get "Yes' and 10 red cells in row 501 of "SomethingElse"

[anmac1789]

I am confused about this line of code:

Private Function RemovePrefix(s As String) As String
RemovePrefix = IIf(Left(s, 4) = "\\?\", Right(s, Len(s) - 4), s)
End Function

What does this mean in relation to

sParentFolder = RemovePrefix(sPathTop)
and
sParentFolder = RemovePrefix(oPath.path)

[snb]

Function F_RemovePrefix(s As String) As String
F_RemovePrefix = mid(s,5)
End Function

But as a function it's unnecessarily complicated

this would do as well


if left(s,4)="\\?\" then s = mid(s,5)

[Paul_Hossler]

I am confused about this line of code:


Private Function RemovePrefix(s As String) As String
RemovePrefix = IIf(Left(s, 4) = "\\?\", Right(s, Len(s) - 4), s)
End Function




The long path names is something I couldn't / didn't test, but the function takes a string and if it begins with "\\?" remove those characters and returns what's left, otherwise it just returns the input string

Instead of using the IIF() function, If / Then / Else / EndIf would have worked

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/iif-function?f1url=%3FappId%3DDev11IDEF1%26l%3Den-US%26k%3Dk(vblr6.chm1012957);k(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue



IIf(expr, truepart, falsepart)

The IIf function syntax has these named arguments (https://docs.microsoft.com/en-us/office/vba/language/glossary/vbe-glossary#named-argument):


Part
Description


expr
Required. Expression that you want to evaluate.


truepart
Required. Value or expression returned if expr is True.


falsepart
Required. Value or expression returned if expr is False.

[Paul_Hossler]

Function F_RemovePrefix(s As String) As String
F_RemovePrefix = mid(s,5)
End Function

But as a function it's unnecessarily complicated

this would do as well


if left(s,4)="\\?\" then s = mid(s,5)

Well, the first version will ALWAYS remove the first 4 characters, even if they're not "\\?"

The use of Mid() might seem to be a little faster (at least less typing) since it replaces a Len() and a Right(), but according to

https://www.aivosto.com/articles/stringopt2.html


Left$, Right$ and Mid$. Performance keeps at the degraded level with this group of functions. These functions create new strings by copying some characters in the input string. These are the only functions that can access the individual characters in a string. As you can see, Mid$ is slower than Left$ or Right$. This means you should use Left$ and Right$ when possible and only resort to Mid$ when you really need to access characters in the middle.

27957

Even called a few 1000 times, either won't make a perceptable wall clock difference. Probably get more performance by not using the Variant form of the functions and using the String versions (Mid$(...) instead of Mid(...)

[snb]

You shouldn't call a function if it shouldn't be applied.
You shouldn't test that after calling the function, but before


Sub M_show()
if left("text"="\\?\" then y = F_RemovePrefix('text")
End Sub

Function F_RemovePrefix(s As String) As String
F_RemovePrefix = mid(s,5)
End Function

I'd prefer, instead of these lines of code:


if left(s,4)="\\?\" then s = mid(s,5)

[Paul_Hossler]

You shouldn't call a function if it shouldn't be applied.
You shouldn't test that after calling the function, but before


Agree, but since the functionality was required in multiple places, I opted to simplify the lines where it was used by putting the IIF() in the function

The largest savings I've seen for 'testing first' is when using Replace().


https://www.aivosto.com/articles/stringopt.html#whyslow




Replace or not?

The following tip might be obvious, but it wasn't to us. It makes no sense to call Replace if you're not likely to replace anything. Replace runs slowly. Replace always creates a copy of the input string, even if no replacement occurs, and making the copy is slow. If a replacement is unlikely, verify first (with InStr (https://www.aivosto.com/articles/instr.html) or InStrB (https://www.aivosto.com/articles/stringopt.html#instrb), for example) that there is something you need to replace.

If InStr(Text$, ToBeReplaced$) <> 0 Then
Text$ = Replace(Text$, ToBeReplaced$, "xyz")
End If

If a replacement is likely or certain to occur, there is no need to call InStr. It just adds an extra burden.

[snb]

You are quoting users with outdated Intel 88 chipsets.
The statement 'Replace runs slowly' is 'so '80-ies' (40 years ago).

[Paul_Hossler]

You are quoting users with outdated Intel 88 chipsets.
The statement 'Replace runs slowly' is 'so '80-ies' (40 years ago).




Ah, well ... so am I

[anmac1789]

So basically in “Excludes_7” workbook, there are several problems:

1. The parent folder displays correctly for files, but does not display correctly for subfolders. For example,

path (column 1): C:\test\subfolder 1\file 1.txt
parent folder (column 2): C:\test\subfolder 1

which is correct

For subfolders,

path (column 1): C:\test\subfolder 1
parent folder (column 2): C:\test\subfolder 1

which is not correct because subfolder 1 resides inside the C:\test folder. it should say, parent folder (column 2): C:\test

The parent folder and the path cannot be the same it just doesn't make sense

2. using the "\\?\ " notation does not display correctly for both column 1 (FILE/FOLDER PATH) and column 2 (PARENT FOLDER)

(post 28)
Agree, but since the functionality was required in multiple places, I opted to simplify the lines where it was used by putting the IIF() in the function You are correct in the sense that removing the brackets from 2 columns was necessary but, in doing so, column 2 had the wrong parent folder path (see #1 above). Therefore, this code needs to be adjusted:



Private Function RemovePrefix(s As String) As String
RemovePrefix = IIf(Left(s, 4) = "\\?\", Right(s, Len(s) - 4), s)
End Function
to firstly, remove longer folder path prefix from both columns and secondly, shorten the parent folder path as #1 above.

I have made a code that shortens what was written in my code


wsOut.Cells(rowOut, colParent).Value = Right(Left(.path, Len(.path) - Len(.Name) - 1), Len(Left(.path, Len(.path) - Len(.Name) - 1)) - 4) to several other ways as shown in the attachment. If it's not shown correctly, then I am uploading my workbook to show those formulas using excel's built-in functions

[Paul_Hossler]

I left .Path in instead of changing it to .ParentFolder when I made some of your other changes and didn't notice the problem

Instead of the worksheet formulas all you need I think is the two changes below

For safety's sake, I added a check to RemovePrefix


Wasn't clear if those were the only issues in your post




Sub Start()
Dim rowStart As Long
' aryExclude = Array( _
' "\\?\D:\Test\111\111CCC\111CCC111", _
' "\\?\D:\Test\333\333AAA", _
' "\\?\D:\Test\333\333BBB" _
' )

' aryExclude = Array("\\?\D:\Test\111")
aryExclude = Array("\\?\D:\Test\222")


Init


rowStart = rowOut
sParentFolder = RemovePrefix(sPathTop)


Call GetFiles(oFSO.GetFolder(sParentFolder)) ' <<<<<<<<<<<<<<<<<<<<<<

wsOut.Cells(rowStart, colFileFolder).Value = "Parent Folder"

If numRuns > 0 Then RemoveDups

Cleanup
End Sub






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


If IsExcluded(oPath) Then Exit Sub ' stops recursion

sParentFolder = RemovePrefix(oPath.ParentFolder) ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Call ListInfo(oPath, "Subfolder")

For Each oFile In oPath.Files
Call ListInfo(oFile, "File")
Next

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

[anmac1789]

it seems like there is still a problem with the parent folder column (column 2). For example,

folder path : C:\test with spaces
parent folder: C:\
which is correct. But,

folder path: C:\test with spaces\file1.txt
parent folder: C:\ ????
which is not correct because, file1.txt resides in the C:\test with spaces\ folder

I just can't seem to understand what & where "s" variable is and where its defined in the code, because the only s i see is in "sParentFolder" is this why you have chosen s?

Private Function RemovePrefix(s As String) As String
If Len(s) < 5 Then
RemovePrefix = s
Else
RemovePrefix = IIf(Left(s, 4) = "\\?\", Right(s, Len(s) - 4), s)
End If
End Function

[Paul_Hossler]

1. Went back to basics with ver 9

27986



2. The 's' is just sort of a placeholder for the function call. When the function REmovePrefix is called a 'real' parameter is passed and the function processes the 'real' one instead of the placeholder 's'

I called it 's' because I was tired of typing (not a good reason) and it was a string. I should called it something more meaningful like "PathToHaveTheBackslashParameterRemoved" which would make the function definition something like



Private Function RemovePrefix(PathToHaveTheBackslashParameterRemoved As String) As String If Len(PathToHaveTheBackslashParameterRemoved) < 5 Then
RemovePrefix = PathToHaveTheBackslashParameterRemoved
Else
RemovePrefix = IIf(Left(PathToHaveTheBackslashParameterRemoved, 4) = "\\?\", Right(PathToHaveTheBackslashParameterRemoved, Len(PathToHaveTheBackslashParameterRemoved) - 4), s)
End If
End Function






3. Single step through the test() sub to see calling parameters in action



Option Explicit

Sub test()
MsgBox RemovePrefix("\\?\SOMETHING")
MsgBox RemovePrefix("\\?\ANOTHER SOMETHING")
MsgBox RemovePrefix("NOTHING")
End Sub








Private Function RemovePrefix(s As String) As String
If Len(s) < 5 Then
RemovePrefix = s
Else
RemovePrefix = IIf(Left(s, 4) = "\\?\", Right(s, Len(s) - 4), s)
End If
End Function

[anmac1789]

2. The 's' is just sort of a placeholder for the function call. When the function REmovePrefix is called a 'real' parameter is passed and the function processes the 'real' one instead of the placeholder 's'okay so I think I kind of understand the placeholder "s" inside the function. When it's used for a actual path then the s gets replaced with something like o.path or whatever and then the path gets the backslack prefix removed. Am I correct in thinking like this?

I have a couple questions

1. When taking further look at your code it seems like (with my n00b basic skills), there could be 2 functions - one function to remove the prefix, second function to take the file/folder path (from column 1) and subtract it from file/folder name to give the parent folder. Does this make the code more efficient or can this be all done within one line of code using the IIF() function?

What I mean by this is for function 1 which removes the prefix to the left of the folder/file path (column 1):

Private Function RemovePrefix(s As String) As String
RemovePrefix = IIf(Left(s, 4) = "\\?\", Right(s, Len(s) - 4), s) 'Right(s, Len(s))
End Function


Function 2 to return the parent folder (column 2) of each folder/file path (from column 1)

Private Function parentfolderfilepath(p As String, s As String) As String
parentfolderfilepath = IIf(Left(p, 4) = "\\?\", Mid(Right(p, Len(p) - 4), 1, Len(Right(p, Len(p) - 4)) - Len(s)), s)
End Function


So combining function 2 and function 1:


wsOut.Cells(rowOut, colPath).Value = RemovePrefix(.path)
wsOut.Cells(rowOut, colParent).Value = parentfolderfilepath(.path, .Name)


But, I just realized that ".ParentFolder" does basically similar to the code above so now which one do I use? Because in 9th revision of the code, it seems like " sParentFolder" is now removed and replaced with ".ParentFolder". Also, what is the difference between these two, they were used in the code in the 7th-9th revision of code, I experimented without .path and it seems to work but i'm just wondering is there any difference?



wsOut.Cells(rowOut, colParent).Value = .ParentFolder.path

AND

wsOut.Cells(rowOut, colParent).Value = .ParentFolder



2. Please take a look at the attachment to see pictures of the problem with "Excludes_9". In this code, after including 2 exclude subfolder paths and executing the code a second time, "subfolder 1" goes missing. I am assuming this also happens with other subfolders even with the same names in deeper levels after the 2nd, 3rd, 4th runs.

The reason why I chose column 1 (file/folder path) to double check duplicates after the 1st run, rather than column 2 (parent folder), is because it uniquely checks each file/subfolder/parent directory during 2nd run, 3rd run, 4th run, etc... for duplicate entries. If parent folder was being checked then it may remove another different file/folder belonging in the same parent directory (ie. having the same parent folder). By seeing that, I made the following changes to the code:


'look at newly added lines (not in rPrev) and if PARENT FOLDER is in rPrev delete from newly added
Private Sub RemoveDups()
Dim rowNew As Long

For rowNew = wsOut.Cells(1, 1).CurrentRegion.Rows.Count To rPrev.Rows.Count + 1 Step -1
If Application.WorksheetFunction.CountIf(rPrev.Columns(colPath), wsOut.Cells(rowNew, colPath).Value) > 0 Then
'mark special
wsOut.Cells(rowNew, colPath).Value = True
End If
Next rowNew

On Error Resume Next
wsOut.Columns(colPath).SpecialCells(xlCellTypeConstants, xlLogical).EntireRow.Delete
On Error GoTo 0


End Sub

Basically, I replaced all instances of
colParent with
colPath. Please let me know if I'm doing it right or wrong (still a vba n00b).

27999

[Paul_Hossler]

okay so I think I kind of understand the placeholder "s" inside the function. When it's used for a actual path then the s gets replaced with something like o.path or whatever and then the path gets the backslack prefix removed. Am I correct in thinking like this?

That's the way that parameters are passed to functions and subs





But, I just realized that ".ParentFolder" does basically similar to the code above so now which one do I use? Because in 9th revision of the code, it seems like " sParentFolder" is now removed and replaced with ".ParentFolder". Also, what is the difference between these two, they were used in the code in the 7th-9th revision of code, I experimented without .path and it seems to work but i'm just wondering is there any difference?


.Path is the default for the Folder object. I just like to specify it

28005



You're using the FileSystemObject which has a rich set of methods and properties

https://ss64.com/vb/filesystemobject.html

The Folder and the File objects have the properties that you were looking to list so it's just easier to use them



' Folder object
' Attributes, DateCreated, DateLastAccessed, DateLastModified, Drive,
' Files, IsRootFolder, Name, ParentFolder (IFolder), Path,
' ShortName, ShortPath, Size, SubFolders, Type


' File object
' Attributes, DateCreated, DateLastAccessed, DateLastModified, Drive (IDrive),
' Name, ParentFolder (IFolder), Path, ShortName, ShortPath, Size, Type
' Attributes




Please take a look at the attachment to see pictures of the problem with "Excludes_9". In this code, after including 2 exclude subfolder paths and executing the code a second time, "subfolder 1" goes missing. I am assuming this also happens with other subfolders even with the same names in deeper levels after the 2nd, 3rd, 4th runs.


What is the definition of 'duplicate' over different runs?



Didn't understand your pictures

With spaces / without spaces don't matter

Test 1 - Do first run and it's Excludes, save copy of Files worksheet as Files-1, and clear Files WS. Is it correct?

Test 2 - Do second run and it's Excludes, save Files worksheet as Files-2, , and clear Files WS.Is it correct?

Test 3 - Do run 1 and it's excludes and then do run 2 it's Excludes. Is it correct?

[anmac1789]

What is the definition of 'duplicate' over different runs?What I mean is, running the code recursively lists all folders/files and repeats the list after each subsequent run producing duplicate entries for the same parent folder. Basically it is this piece of code here:


Private Sub RemoveDups()
Dim rowNew As Long

For rowNew = wsOut.Cells(1, 1).CurrentRegion.Rows.Count To rPrev.Rows.Count + 1 Step -1
If Application.WorksheetFunction.CountIf(rPrev.Columns(colParent), wsOut.Cells(rowNew, colParent).Value) > 0 Then
'mark special
wsOut.Cells(rowNew, colParent).Value = True
End If
Next rowNew

On Error Resume Next
wsOut.Columns(colParent).SpecialCells(xlCellTypeConstants, xlLogical).EntireRow.Delete
On Error GoTo 0


End Sub

However, I just thought of something. What if I produce a list of files and folders for a parent directory in run 1 but want to exclude a folder path AFTER run 1 for run 2 and shorten the results? Would I just need to add another code or can I extend the above code?



Didn't understand your pictures

With spaces / without spaces don't matter

Test 1 - Do first run and it's Excludes, save copy of Files worksheet as Files-1, and clear Files WS. Is it correct?

Test 2 - Do second run and it's Excludes, save Files worksheet as Files-2, , and clear Files WS.Is it correct?

Test 3 - Do run 1 and it's excludes and then do run 2 it's Excludes. Is it correct? The reason for the spaces inside the folder names is just something random I did, i understand it doesn't matter and that excel vba accepts spaces in file/folder names. The pictures demonstrate that after run 2, a folder from run 1 was deleted. I am attaching a workbook which explains what those pictures mean

[Paul_Hossler]

However, I just thought of something. What if I produce a list of files and folders for a parent directory in run 1 but want to exclude a folder path AFTER run 1 for run 2 and shorten the results? Would I just need to add another code or can I extend the above code?

Using my folder structure I made 4 runs

1. D:\Test with no Excludes - gave row 2 - 104 on 'Files'

2. D:\Test2 with no Excludes - gave row 105 - 206 on 'Files'

3. D:\Test with no Excludes (same as run #1) - added the lines 207 - 309 when recursion, but then deleted them as dups from run #1

4. D:\Test with Excludes - added the lines 207 - 271 when recursion, but then deleted them as dups from run #1


Now ....

Are you saying that you want to always (a) remove duplicate PATHs from the entire list after each run, or (b) duplicate PARENT FOLDERs from the entire list after each run, or (c) something else?

(a) will delete duplicate FILES and (b) will delete duplicate folders and all the files in the folder from the list


Right now, only the new appended lines are check for dups

28010

[anmac1789]

I tested out revision 10 of the workbook and still having the same problem

I want to do:

(a) remove duplicate PATHs from the entire list after each run by checking from column A and
(b) will delete duplicate folders and all the files in the folder from the list by checking from column A for each run. I am attaching a workbook to illustrate the problem from 2 runs

Can you send me your directory files so I can work with it as well ?

[Paul_Hossler]

If all you want to do is keep a 'running list' of PATHs that have passed, a simple .RemoveDuplicates() would work




Private Sub RemoveDups()
wsOut.Cells(1, 1).CurrentRegion.RemoveDuplicates Columns:=1, Header:=xlYes
End Sub



In attached sheet 'BeforeRemovingDuplicates'

Green = Test2 with no excludes

Yellow= Test with Test\222 excluded

Blue = Test2 again with no excluded BEFORE removing duplicates

Sheet 'Files' is final result

I attached a Tree listing of my two test folder structures

[anmac1789]

If all you want to do is keep a 'running list' of PATHs that have passed, a simple .RemoveDuplicates() would workWhat do you mean by this?

[Paul_Hossler]

What do you mean by this?

Every run will add the files/folders that are not excluded by that run and any paths added by that run which are already in the list will be deleted

[anmac1789]

I was just wondering why do you have a second module with this code:


Option Explicit

Sub Macro1()
'
' Macro1 Macro
'


'
ActiveSheet.Range("$A$1:$H$104").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub
Sub Macro2()
'
' Macro2 Macro
'


'

End Sub


when you have :


Private Sub RemoveDups() wsOut.Cells(1, 1).CurrentRegion.RemoveDuplicates Columns:=1, Header:=xlYes
End Sub and also from here:

Call GetFiles(oFSO.GetFolder(RemovePrefix(sPathTop)))
wsOut.Cells(rowStart, colFileFolder).Value = "Parent Folder"

RemoveDups

Cleanupwhich already removes duplicates as intended

Also, i have tested out a parent folder path which is 243 characters long and the files within it did not get listed even thought I have attached a " \\?\ " prefix to take care of long file names...

[Paul_Hossler]

1. I recorded Macro1() just to get an idea of the syntax to incorporate and forgot to delete it. You can delete it

2. I didn't make any extra long folders. Try removing the RemovePrefix logic and see if that works

[anmac1789]

2. I didn't make any extra long folders. Try removing the RemovePrefix logic and see if that worksDo you mean this logic here:

Private Function RemovePrefix(s As String) As String
If Len(s) < 5 Then
RemovePrefix = s
Else
RemovePrefix = IIf(Left(s, 4) = "\\?\", Right(s, Len(s) - 4), s)
End If
End Function

[Paul_Hossler]

Did some experimenting and the results were confusing. I'm not too sure the FSO treats "\\?" consistently


Try this and see if you can get rid of using the "\\?"



https://www.itprotoday.com/windows-10/enable-long-file-name-support-windows-10


Q. How do I enable long file name support in Windows 10?A. In the past the maximum supported file length was 260 characters (256 usable after the drive characters and termination character). In Windows 10 long file name support can be enabled which allows file names up to 32,767 characters (although you lose a few characters for mandatory characters that are part of the name). To enable this perform the following:


Start the registry editor (regedit.exe)
Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem
Double click LongPathsEnabled
Set to 1 and click OK
Reboot

This can also be enabled via Group Policy via Computer Configuration > Administrative Templates > System > Filesystem > Enable NTFS long paths.

[anmac1789]

oh...ive done that already and its enabled. When I disabled
ActiveSheet.Range("$A$1:$H$104").RemoveDuplicates Columns:=1, Header:=xlYes and ran my code on the long folder path I got a run-time error '76': path not found...

[anmac1789]

Any cause for the error in the previous post?

[Paul_Hossler]

Any cause for the error in the previous post?

Couldn't tell you. My macro doesn't generate any errors:



Private Sub RemoveDups() wsOut.Cells(1, 1).CurrentRegion.RemoveDuplicates Columns:=1, Header:=xlYes
End Sub



But I explicitly specify the worksheet (wsOut and not ActiveSheet) and explicitly use the entire CurrentRegion (and not hardcoded A1:H104)

If I had to guess, I'd suspect that the currently active worksheet is not the one with the file data

[anmac1789]

This is in response to the previous post. I am attaching 2 workbook so you can take a closer look at the errors

[Paul_Hossler]

Afraid I'm not seeing the errors when I test using my shorter folder trees

I'm not sure what would caused the CountIf 1004 error. A manual WS formula works fine

Your top path is 290 characters and I tried to copy it, but bumped up against the limit and I didn't want to change my PC configuration to try and go longer

and there seems to be a lot of redundancy and very long folder names

Could you get it to something shorter?

[anmac1789]

The previous versions of excludes seemed to work perfectly fine and it was showing up very long folder paths without errors. I dont' know why but just yesterday I was having problems listing long folder paths, it didn't have this kind of error before...

may i ask please not to show the full folder path, I just didn't want to show my name...let me change the names out and put something random

Would it be possible just to enable long folder paths in your registry just for this problem?

[Paul_Hossler]

OK ...

Note that my top path is on D: so change it to C: if necessary




Option Explicit


Const sPathTop As String = "\\?\D:\test one"


Const colPath As Long = 1
Const colParent As Long = 2
Const colName As Long = 3
Const colFileFolder As Long = 4
Const colCreated As Long = 5
Const colModified As Long = 6
Const colSize As Long = 7
Const colType As Long = 8


Dim aryExclude As Variant
Dim rowOut As Long
Dim oFSO As Object
Dim wsOut As Worksheet
Dim rPrev As Range


Sub Start()
Dim rowStart As Long
Dim oFile As Object

' aryExclude = Array("\\?\C:\test\subfolder 1", "\\?\C:\test\subfolder 2", "\\?\C:\test\subfolder 3")
aryExclude = Array("")


Init


rowStart = rowOut


Call GetFiles(oFSO.GetFolder(sPathTop))

wsOut.Cells(rowStart, colFileFolder).Value = "Parent Folder"

RemoveDups

Cleanup
End Sub






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


If IsExcluded(oPath) Then Exit Sub ' stops recursion

Call ListInfo(oPath, "Subfolder")

For Each oFile In oPath.Files
Call ListInfo(oFile, "File")
Next

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


'========================================================================== ==
Private Sub Init()
Dim i As Long

Application.ScreenUpdating = False

If IsArray(aryExclude) Then
For i = LBound(aryExclude) To UBound(aryExclude)
aryExclude(i) = CStr(aryExclude(i))
Next i
End If

Set wsOut = Worksheets("Files")

With wsOut
'get last used row, or 1 if empty
rowOut = .Cells(.Rows.Count, 1).End(xlUp).Row

If rowOut = 1 Then ' blank sheet
.Cells(rowOut, colPath).Value = "FILE/FOLDER PATH"
.Cells(rowOut, colParent).Value = "PARENT FOLDER"
.Cells(rowOut, colName).Value = "FILE/FOLDER NAME"
.Cells(rowOut, colFileFolder).Value = "FILE or FOLDER"
.Cells(rowOut, colCreated).Value = "DATE CREATED"
.Cells(rowOut, colModified).Value = "DATE MODIFIED"
.Cells(rowOut, colSize).Value = "SIZE"
.Cells(rowOut, colType).Value = "TYPE"
End If

rowOut = rowOut + 1

'save the previous data
Set rPrev = wsOut.Cells(1, 1).CurrentRegion
End With

Set oFSO = CreateObject("Scripting.FileSystemObject")
End Sub


Private Sub Cleanup()
wsOut.Columns(colName).HorizontalAlignment = xlLeft
wsOut.Columns(colCreated).NumberFormat = "m/dd/yyyy"
wsOut.Columns(colModified).NumberFormat = "m/dd/yyyy"
wsOut.Columns(colSize).NumberFormat = "#,##0,.0 ""KB"""

wsOut.Cells(1, 1).CurrentRegion.EntireColumn.AutoFit

Application.ScreenUpdating = True


MsgBox "Done"
End Sub


Private Sub RemoveDups()
wsOut.Cells(1, 1).CurrentRegion.RemoveDuplicates Columns:=1, Header:=xlYes
End Sub


' IFolder object
' Attributes, DateCreated, DateLastAccessed, DateLastModified, Drive,
' Files, IsRootFolder, Name, ParentFolder (IFolder), Path,
' ShortName, ShortPath, Size, SubFolders, Type


' iFile object
' Attributes, DateCreated, DateLastAccessed, DateLastModified, Drive (IDrive),
' Name, ParentFolder (IFolder), Path, ShortName, ShortPath, Size, Type
' Attributes


Private Sub ListInfo(oFolderFile As Object, sType As String)
With oFolderFile
wsOut.Cells(rowOut, colPath).Value = .Path
wsOut.Cells(rowOut, colParent).Value = oFSO.GetParentFolderName(.Path) ' <<<<<<<<<<
wsOut.Cells(rowOut, colName).Value = .Name
wsOut.Cells(rowOut, colFileFolder).Value = sType
wsOut.Cells(rowOut, colCreated).Value = .DateCreated
wsOut.Cells(rowOut, colModified).Value = .DateLastModified
wsOut.Cells(rowOut, colSize).Value = .Size
wsOut.Cells(rowOut, colType).Value = .Type
End With

rowOut = rowOut + 1
End Sub


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

If IsEmpty(aryExclude) Then
IsExcluded = False
Exit Function
End If

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

[snb]

See:

https://www.snb-vba.eu/VBA_Recursion_en.html#L_6.1

[anmac1789]

I want to extend my code to have a parent folder list in column J and an exclude list in column K. The below code is in module 2. I found that instead of writing each parent folder in "sPathTop", I could've listed the parent folders in column J (or any other column) and then ran the main code. I just don't know how to run the main code for each parent folder in column J while taking into account the exclude list in column K. In other words, how can I include the below code to run with my main code? Thanks


Sub examplearray()


Dim testarray() As String, size As Integer, i As Integer, x As Variant


size = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlDown)))


ReDim testarray(size)

'Range("L2") = LBound(testarray)
'Range("L3") = UBound(testarray)


For i = 1 To size
testarray(i) = Range("A" & i).Value
Next i


End Sub

[anmac1789]

It seems like there is a number of changes in the current version of excludes_14 from previous versions:
These have been removed, from comparing the older versions of exclude_#.xlsx with excludes_14.xlsx
1.
Dim numRuns As Long has been removed
2.

'see how many runs were packed in by counting "Parent Folder"
numRuns = Application.WorksheetFunction.CountIf(wsOut.Columns(colFileFolder), "Parent Folder")
has been removed
3.
If numRuns > 0 Then RemoveDupshas been removed
4.
Private Sub RemoveDups()
Dim rowNew As Long

For rowNew = wsOut.Cells(1, 1).CurrentRegion.Rows.Count To rPrev.Rows.Count + 1 Step -1
If Application.WorksheetFunction.CountIf(rPrev.Columns(colParent), wsOut.Cells(rowNew, colParent).Value) > 0 Then
'mark special
wsOut.Cells(rowNew, colParent).Value = True
End If
Next rowNew

On Error Resume Next
wsOut.Columns(colParent).SpecialCells(xlCellTypeConstants, xlLogical).EntireRow.Delete
On Error GoTo 0


End Sub
has been changed to:


Private Sub RemoveDups()
wsOut.Cells(1, 1).CurrentRegion.RemoveDuplicates Columns:=1, Header:=xlYes
End Sub
Does this new code do exactly the above code and only keeps 1 type of entry and removes all other duplicates from column 1?

Also,
wsOut.Cells(rowOut, colParent).Value = RemovePrefix(.ParentFolder.path)has been modified to
wsOut.Cells(rowOut, colParent).Value = oFSO.GetParentFolderName(.Path)isnt .ParentFolder.path the same thing as oFSO.GetParentFolderName(.Path) ??


Private Function RemovePrefix(s As String) As String
If Len(s) < 5 Then
RemovePrefix = s
Else
RemovePrefix = IIf(Left(s, 4) = "\\?\", Right(s, Len(s) - 4), s)
End If
End Function
has been removed.. Why is this so?



For i = LBound(aryExclude) To UBound(aryExclude)
aryExclude(i) = RemovePrefix(CStr(aryExclude(i)))
Next i

has been changed to:

For i = LBound(aryExclude) To UBound(aryExclude)
aryExclude(i) = CStr(aryExclude(i))
Next i
This only removes the long folder path prefix from the exclude list and not the parent folder column (2). It was done before in previous workbooks but not the new excludes_14...

How come:
Call GetFiles(oFSO.GetFolder(RemovePrefix(sPathTop)))generates error message saying "Run time error '76': Path not found" for the same parent folder even if its a longer folder path exceeding 260 characters??

[anmac1789]

looking back at excludes_5,

I had a code which highlights duplicates in red colour using the below code:

module 2 code:

Option Explicit


Sub sbFindDuplicatesInColumn_C()
Dim i As Long


'Declaring the lastRow variable as Long to store the last row value in the Column1
Dim lastRow As Long


'matchFoundIndex is to store the match index values of the given value
Dim matchFoundIndex As Long


'iCntr is to loop through all the records in the column 1 using For loop
Dim iCntr As Long


'Finding the last row in the Column 1
lastRow = Cells(Rows.Count, "A").End(xlUp).Row 'Range("A65000").End(xlUp).Row


'looping through the column1
For iCntr = 1 To lastRow
'checking if the cell is having any item, skipping if it is blank.
If Cells(iCntr, 1) <> "" Then
'getting match index number for the value of the cell
matchFoundIndex = WorksheetFunction.Match(Cells(iCntr, 1), Range("A1:A" & lastRow), 0)
'if the match index is not equals to current row number, then it is a duplicate value
If iCntr <> matchFoundIndex Then
'Printing the label in the column B
Cells(iCntr, 1).Interior.Color = RGB(255, 12, 0)
Cells(iCntr, 2) = "there are duplicates here!"
End If
End If
Next
End Sub

For a path that is for example, 52 characters long the above code highlights duplicates in column 1. But, I have a path that is 611 characters long, and for some reason it does not detect duplicates OR highlights them, instead returns an error message:

run-time error 13: type mismatch, when I click debug it highlights "matchFoundIndex = WorksheetFunction.Match(Cells(iCntr, 1), Range("A1:A" & lastRow), 0)" in module 2

Main code in module 1:


Option Explicit


'>>>> this is for a path that is 52 characters long, including the long folder path prefix in the beginning
'>>>> duplicates are detected for this path (in column 1) and removed properly
'Const sPathTop As String = "\\?\C:\Users\abcde\Downloads\downloads abc 123 docs"


'>>>> this is for a path that is 611 characters long, including the long folder path prefix in the beginning
'>>>> duplicates are NOT detected for this path (in column 1) and gives run-time error 13: type mismatch, highlighting "highlighting matchFoundIndex = WorksheetFunction.Match(Cells(iCntr, 1), Range("A1:A" & lastRow), 0)" in module 2
Const sPathTop As Variant = "\\?\C:\Users\nrhfy\Downloads\seagate 500\Documents and Settings\abcd\AppData\Local\Application Data\Application Data\Application Data\Application Data\Application Data\Application Data\Application Data\Application Data\Application Data\Application Data\Application Data\Application Data\Application Data\Application Data\Application Data\Application Data\Application Data\Application Data\Application Data\Application Data\Application Data\Application Data\Application Data\Application Data\Application Data\Application Data\Application Data\Application Data\Application Data\Application Data\Adobe\Acrobat\11.0"


Const colPath As Long = 1
Const colParent As Long = 2
Const colName As Long = 3
Const colFileFolder As Long = 4
Const colCreated As Long = 5
Const colModified As Long = 6
Const colSize As Long = 7
Const colType As Long = 8


Dim aryExclude As Variant
Dim rowOut As Long
Dim oFSO As Object
Dim wsOut As Worksheet
Dim sParentFolder As Variant


Sub Start()
Dim rowStart As Long

aryExclude = Array("")

Init


rowStart = rowOut
sParentFolder = RemovePrefix(sPathTop)


Call GetFiles(oFSO.GetFolder(sPathTop))

wsOut.Cells(rowStart, colFileFolder).Value = "Parent Folder"

'wsOut.Columns(5).NumberFormat = "m/dd/yyyy"
'wsOut.Columns(6).NumberFormat = "m/dd/yyyy"
'wsOut.Columns(7).NumberFormat = "#,##0,.0 ""KB"""


End Sub


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


If IsExcluded(oPath) Then Exit Sub ' stops recursion

sParentFolder = IIf(Left(oPath.path, 4) = "\\?\", Right(oPath.path, Len(oPath.path) - 4), oPath.path)

Call ListInfo(oPath, "Subfolder")

For Each oFile In oPath.Files
Call ListInfo(oFile, "File")
Next

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


'========================================================================== ==
Private Sub Init()
Set wsOut = Worksheets("Sheet2")

With wsOut

rowOut = .Cells(.Rows.Count, 1).End(xlUp).Row

If rowOut = 1 Then ' blank sheet
.Cells(rowOut, 1).Value = "FILE/FOLDER PATH"
.Cells(rowOut, 2).Value = "PARENT FOLDER"
.Cells(rowOut, 3).Value = "FILE/FOLDER NAME"
.Cells(rowOut, 4).Value = "FILE or FOLDER"
.Cells(rowOut, 5).Value = "DATE CREATED"
.Cells(rowOut, 6).Value = "DATE MODIFIED"
.Cells(rowOut, 7).Value = "SIZE"
.Cells(rowOut, 8).Value = "TYPE"
End If

rowOut = rowOut + 1

End With


Set oFSO = CreateObject("Scripting.FileSystemObject")
End Sub


' IFolder object
' Attributes, DateCreated, DateLastAccessed, DateLastModified, Drive,
' Files, IsRootFolder, Name, ParentFolder (IFolder), Path,
' ShortName, ShortPath, Size, SubFolders, Type


' iFile object
' Attributes, DateCreated, DateLastAccessed, DateLastModified, Drive (IDrive),
' Name, ParentFolder (IFolder), Path, ShortName, ShortPath, Size, Type
' Attributes


Private Sub ListInfo(oFolderFile As Object, sType As String)
With oFolderFile
wsOut.Cells(rowOut, colPath).Value = RemovePrefix(.path)
wsOut.Cells(rowOut, colParent).Value = RemovePrefix(Left(.path, Len(.path) - Len(.Name) - 1))
wsOut.Cells(rowOut, colName).Value = .Name
wsOut.Cells(rowOut, colFileFolder).Value = sType
wsOut.Cells(rowOut, colCreated).Value = .DateCreated
wsOut.Cells(rowOut, colModified).Value = .DateLastModified
wsOut.Cells(rowOut, colSize).Value = .Size
wsOut.Cells(rowOut, colType).Value = .Type
End With

rowOut = rowOut + 1
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


Private Function RemovePrefix(s As String) As String
If Len(s) < 5 Then
RemovePrefix = s
Else
RemovePrefix = IIf(Left(s, 4) = "\\?\", Right(s, Len(s) - 4), s)
End If
End Function
I am not understanding why it would not highlight because i can list the folders with long folder path recursively without errors and I would think that highlighting doesn't depend on the number of characters in a string...what is the solution to this?

[Paul_Hossler]

For a path that is for example, 52 characters long the above code highlights duplicates in column 1. But, I have a path that is 611 characters long, and for some reason it does not detect duplicates OR highlights them, instead returns an error message:

run-time error 13: type mismatch, when I click debug it highlights "matchFoundIndex = WorksheetFunction.Match(Cells(iCntr, 1), Range("A1:A" & lastRow), 0)" in module 2




https://exceljet.net/formula/match-long-text#:~:text=The%20MATCH%20function%20has%20a,to%20parse%20and%20compare%20 text.


[QUOTE]
The MATCH function has a limit of 255 characters for the lookup value. If you try to use longer text, MATCH will return a #VALUE error. To workaround this limit you can use boolean logic and the LEFT, MID, and EXACT functions to parse and compare text./QUOTE]


You could generate a hash code for each entry and MATCH() against those

[anmac1789]

You could generate a hash code for each entry and MATCH() against those




What does this mean?

[Paul_Hossler]

LMGTFY

https://en.wikipedia.org/wiki/Hash_function


A hash function is any function (https://en.wikipedia.org/wiki/Function_(mathematics)) that can be used to map data (https://en.wikipedia.org/wiki/Data_(computing)) of arbitrary size to fixed-size values. The values returned by a hash function are called hash values, hash codes, digests, or simply hashes. The values are usually used to index a fixed-size table called a hash table (https://en.wikipedia.org/wiki/Hash_table). Use of a hash function to index a hash table is called hashing or scatter storage addressing.


I had a Hash module that I added to the attachment.
The test data in the attachment (your folder tree) was order randomized
The MarkDupsWithHash sub was run to mark dups in red
The data was resorted as a check




Option Explicit


Sub MarkDupsWithHash()
Dim r As Range
Dim aryHash() As String
Dim i As Long, j As Long

'test
Worksheets("Files").Columns(1).Interior.ColorIndex = xlColorIndexNone


Set r = Worksheets("Files").Cells(1, 1)
Set r = Range(r, r.End(xlDown))


ReDim aryHash(1 To r.Rows.Count)


For i = LBound(aryHash) To UBound(aryHash)
aryHash(i) = CreateSHA256HashString(r.Cells(i, 1).Value)
Next i


For i = LBound(aryHash) To UBound(aryHash) - 1
For j = i + 1 To UBound(aryHash)
If aryHash(j) = aryHash(i) Then r.Cells(j, 1).Interior.Color = vbRed
Next j
Next i


MsgBox "Done"


End Sub




If you don't want to use a Hash, then the For i / For j loops should also work, but will be slower I think

[anmac1789]

I was looking at worksheetfunction.unique() from this website: https://www.reddit.com/r/excel/comments/9w54o5/dynamic_array_formulas_new_functions_and_methods/ to extract unique values but it seem it doesn't print the unique values to a range, it only stores them within each element of the array

[anmac1789]

I am trying to use cells to execute my code for each folder path and run my code but the furthest that I have gone is just getting an example of a for each...next loop and don't know how to proceed. Here is my code:


Sub example()


Dim cell As Range


For Each cell In Range("A2", Range("A2").End(xlDown))
cell.Font.Color = 255
Next cell


End Sub

The problem with this code is that if only cell A2 is filled in and then I run Sub example(), I get a "Code execution has been interrupted" error. However, when I have 2 cells filled in, cell A2 and A3, then there is no error. Why doesn't one cell (cell A2) work ??

This is an example, but what I want to do is instead of manually inserting each file/folder path inside sPathTop and executing my main code, I want to manually write the folder path(s) in suppose cell A1 and then run my main code on this folder path into another worksheet. This way, I wouldn't have to go back and change sPathTop for each additional folder path that I add. it solves one problem but it gives another. Finding duplicates is already inserted on a different sub which we solved using hashes, but another problem is to also include a exclude folder path list suppose in cell B1.. I have went back to the main code and i see that spathtop is a constant but How do I store different folder paths inside a range, its making me think of arrays..so I'm not sure. How do I proceed? Thanks...

[Paul_Hossler]

Changes and comments/suggestions



Option Explicit


Sub example()
'better not to use VBA reserved words, less confusing
Dim rCell As Range


'as it was, whatever the activesheet was would be used
.need the dot on the Range(A2)'s
With Worksheets("Sheet1")


'the Range around (A2) was missing
For Each rCell In Range(.Range("A2"), .Range("A2").End(xlDown))
rCell.Font.Color = 255
Next
End With
End Sub




Suggestion: by starting in A2 and going down, the selection will end at a cell above a blank cell, and if there are data filled cells below, they won't be included

I've found it's safer to start at the bottom of the worksheet and go up to make the end cell the one with data



Sub example2()
Dim rCell As Range, rColor As Range


With Worksheets("Sheet1")
Set rColor = .Range("A2")
Set rColor = Range(rColor, .Cells(.Rows.Count, 1).End(xlUp))

'the Range around (A2) was missing
For Each rCell In rColor.Cells
rCell.Font.Color = 255
Next
End With
End Sub

[anmac1789]

This is an example, but what I want to do is instead of manually inserting each file/folder path inside sPathTop and executing my main code, I want to manually write the folder path(s) in suppose cell A1 and then run my main code on this folder path into another worksheet. This way, I wouldn't have to go back and change sPathTop for each additional folder path that I add. it solves one problem but it gives another. Finding duplicates is already inserted on a different sub which we solved using hashes, but another problem is to also include a exclude folder path list suppose in cell B1.. I have went back to the main code and i see that spathtop is a constant but How do I store different folder paths inside a range, its making me think of arrays..so I'm not sure. How do I proceed? Thanks...
What if I wanted to extend the red cell method for file/folder paths in my main code ??

[Paul_Hossler]

This loops a list of 3 folders

Within the loop, you could use Dir() to get the files, etc.






Option Explicit


Sub example3()
Dim vPath As Variant


For Each vPath In Array("C:\Users", "D:\Music", "L:\Quicken")

MsgBox vPath & " -- " & FileDateTime(vPath)

Next
End Sub

[anmac1789]

Within the loop, you could use Dir() to get the files, etc.


is it possible to store cell values of a range and reference them in an array using a variable which dynamically shortens or lengthens and then use a Call statement to execute a main code for each element inside that array ?

[Paul_Hossler]

Yes, not sure about the "which dynamically shortens or lengthens" part


Option Explicit

Sub LoadArray()
Dim a As Variant
Dim B As Variant
Dim i As Long

'needed to make a 1 dim array
a = Application.WorksheetFunction.Transpose(ActiveSheet.Cells(1, 1).CurrentRegion)


'we can use this as a 2 dim array
B = ActiveSheet.Cells(1, 3).CurrentRegion

For i = LBound(a) + 1 To UBound(a)
Call SubA(a(i))
Next i

For i = LBound(B, 1) + 1 To UBound(B, 1)
Call SubB(B(i, 1), B(i, 2))
Next i



End Sub


Sub SubA(N As Variant)
MsgBox "SubA " & 10 * N
End Sub


Sub SubB(N1 As Variant, N2 As Variant)
MsgBox "SubB " & N1 * N2
End Sub

[anmac1789]

Yes, not sure about the "which dynamically shortens or lengthens" partWhat I mean by this is that the number of entries dynamically changes with each folder path addition or deletion.

Lets say in Sheet1 I have:

cell A2 = folderpath1
cell A3 = folderpath2
cell A3 = folderpath3

How can I execute the below code on each folder path above and get the result of the search in Sheet2 ??


Private Sub ListInfo(oFolderFile As Object, sType As String)
With oFolderFile
wsOut.Cells(rowOut, colPath).Value = RemovePrefix(.Path)
wsOut.Cells(rowOut, colParent).Value = RemovePrefix(oFSO.GetParentFolderName(.Path)) 'oFSO.GetParentFolderName(.Path) or .ParentFolder.Path
wsOut.Cells(rowOut, colName).Value = .Name
wsOut.Cells(rowOut, colFileFolder).Value = sType
wsOut.Cells(rowOut, colCreated).Value = .DateCreated
wsOut.Cells(rowOut, colModified).Value = .DateLastModified
wsOut.Cells(rowOut, colSize).Value = .size
wsOut.Cells(rowOut, colType).Value = .Type
End With


Please take a look at the example workbook attached..

[Paul_Hossler]

Code fragment that may help




Option Explicit


Dim rowNext As Long
Dim ws1 As Worksheet, ws2 As Worksheet



Sub LoadArray()
Dim aryFolders As Variant
Dim aryParameters As Variant
Dim i As Long

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

'needed to make a 1 dim array
aryFolders = Application.WorksheetFunction.Transpose(ws1.Cells(1, 1).CurrentRegion)


'next blank row
rowNext = ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Offset(1, 0).Row

For i = LBound(aryFolders) + 1 To UBound(aryFolders)
Call ListInfo(aryFolders(i), i)
Next i

End Sub




Sub ListInfo(S As Variant, N As Long)
'Sub ListInfo(oFolderFile As Object, sType As String)
' With oFolderFile
' wsOut.Cells(rowOut, colPath).Value = RemovePrefix(.Path)
' wsOut.Cells(rowOut, colParent).Value = RemovePrefix(oFSO.GetParentFolderName(.Path)) 'oFSO.GetParentFolderName(.Path) or .ParentFolder.Path
' wsOut.Cells(rowOut, colName).Value = .Name
' wsOut.Cells(rowOut, colFileFolder).Value = sType
' wsOut.Cells(rowOut, colCreated).Value = .DateCreated
' wsOut.Cells(rowOut, colModified).Value = .DateLastModified
' wsOut.Cells(rowOut, colSize).Value = .Size
' wsOut.Cells(rowOut, colType).Value = .Type
' End With


With ws2.Rows(rowNext)
.Cells(1).Value = S
.Cells(2).Value = N
.Cells(3).Value = 2 * N
.Cells(4).Value = 4 * N
.Cells(5).Value = N ^ 2
.Cells(6).Value = N / 2
End With

rowNext = rowNext + 1
End Sub

[anmac1789]

But, how can I use the filesystemobject properties together with arrays ?

[Paul_Hossler]

I don't have your latest version, but this is one way to store the FSO properties in an array

The For/Next and Do/Loop will need to be integrated into the overall macro



Option Explicit


Dim aryCount As Long
Dim aryData(1 To 1000, 1 To 8) As Variant


Dim ws1 As Worksheet, ws2 As Worksheet



Sub LoadArray()
Dim aryFolders As Variant
Dim i As Long

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

'needed to make a 1 dim array
aryFolders = Application.WorksheetFunction.Transpose(ws1.Cells(1, 1).CurrentRegion)


aryCount = 0

For i = LBound(aryFolders) + 1 To UBound(aryFolders)

'do loop files and folder in aryFolders
Call ListInfo(FileorFolderObject, FileorFolderType)
'loop
Next i
End Sub




Sub ListInfo(oFolderFile As Object, sType As String)
aryCount = aryCount + 1
With oFolderFile
aryData(aryCount, 1) = RemovePrefix(.Path)
aryData(aryCount, 2) = RemovePrefix(oFSO.GetParentFolderName(.Path))
aryData(aryCount, 3) = .Name
aryData(aryCount, 4) = sType
aryData(aryCount, 5) = .DateCreated
aryData(aryCount, 6) = .DateLastModified
aryData(aryCount, 7) = .Size
aryData(aryCount, 8) = .Type
End With
End Sub

[anmac1789]

The latest version was excludes_14 very little has changed in formatting or newer codes...
It says that variable not defined and "FileorFolderObject" is highlighted

[snb]

The code in #71 rewritten.

Reduce the amount of variables to a minimum.
Use Arrays to read data, to store calculated/adapted data, and to write them in 1 movement into the workbook.
Reduce the interaction with the workbook to: reading once, writing once.


Sub M_snb()
sn = Sheet1.Cells(1).CurrentRegion.Resize(, 9) ' reading

With CreateObject("scripting.filesystemobject")
For j = 1 To UBound(sn)
With .getfolder(sn(j, 1))
For jj = 2 To UBound(sn, 2)
sn(j, jj) = Choose(jj, "", .Path, .Drive, .Name, .Type, .datecreated, .datelastmodified, .Size, .Type)
Next
End With
Next
End With

Sheet1.Cells(1).CurrentRegion.Resize(, 9) = sn ' writing
End Sub

[Paul_Hossler]

1. I attached an expanded copy of your test folder (this is my folder files to test on.zip)

2. Excludes_15 has a loop to read the folders to recurse on the worksheet FoldersToDo. Includes a second call to a folder to test dup removal

3. Remove Dups looks in column A of the output sheet since that's what Excluded_14 does

4. There is no need avoid writing directly to the output worksheet. It goes quickly enough

5. Folders to exclude can also be a list on FoldersToDo




Edit --

I decided to use a different approach for collecting data in ver 17

[anmac1789]

5. Folders to exclude can also be a list on FoldersToDoDoes this go in column 2 of 'FoldersToDo' worksheet ? because I see 'Generate Duplicates' is added there..

[Paul_Hossler]

No, that was just a note that the second call to that folder will Generate Duplicates, and I wanted to make sure that they were removed

Probably something like this for the Excludes

I'm still not sure about how you want to define what gets excluded when you have an Excluded Folder

28430


Decided to go a different approach for collecting file/folder data in ver 17

[anmac1789]

I'm still not sure about how you want to define what gets excluded when you have an Excluded Folderso basically, if a folder is listed as being excluded, then everything inside that parent folder and the parent folder itself should get excluded I would say...if it were written as a path that would make it easier because it pinpoints exactly what gets excluded weather its a entire folder path or a specific set of file(s)

[Paul_Hossler]

so basically, if a folder is listed as being excluded, then everything inside that parent folder and the parent folder itself should get excluded I would say...if it were written as a path that would make it easier because it pinpoints exactly what gets excluded weather its a entire folder path or a specific set of file(s)


Check that Excludes_17.xlsm does it that way

[anmac1789]

it only changes with the folder/file name. So I changed this section of the code:


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


Edit: I Just found out it only excludes folders not actual file(s). I will try to find some kind of readjustment to exclude in addition to file path(s)

[Paul_Hossler]

Not seeing that

I think it excludes the folders in the Col C list and the files within those folders/subfolders

28436

'subfolder 2' is on the Exclude list and a 'Find' on the listings in Col A does not see it

I prefixed the files in 'subfolder 2' with 'Exclude' just to make sure

So if that's NOT what you want, you'll need to be more specific

[anmac1789]

For example, in the exclude list, I can exclude subfolders such as "C:\Users\azeem\Downloads\Test Files\test one\subfolder 2" but lets say I want to exclude a specific file inside that folder "C:\Users\azeem\Downloads\Test Files\test one\subfolder 2\Exclude file2-1.txt" not necessary the entire subfolder

[Paul_Hossler]

I added in Col E a list of files that are always excluded regardless of what folder they're in

If a folder is excluded in Col C that all subfolders and all files are excluded

28439

so File1.txt and File2.txt are excluded

In ver 18 the gray shaded are not included

[anmac1789]

ohh i see it now...so I can change
If UCase(p.Name) = UCase(aryFiles(i)) Then Exit Function for both isFolderExcluded and isFileExcluded into something like
If UCase(p.path) = UCase(aryFiles(i)) Then Exit Function if I wanted the complete path for each specific folder or specific file to be excluded....

[Paul_Hossler]

All I can say is ...


1. This recursively goes down all the paths in Col A



For i = LBound(aryPaths) To UBound(aryPaths)
Call GetFiles(oFSO.GetFolder(RemovePrefix(aryPaths(i))))
Next




2. If the path is in Col C, then it stops going down that path



If isFolderExcluded(oPath) Then Exit Sub ' stops recursion

Call AddFileFolder(oPath)




3. If the file name is in Col E then it does NOT get added to the list



For Each oFile In oPath.Files
If Not isFileExcluded(oFile) Then Call AddFileFolder(oFile)
Next



4. So I'm not sure how to interpret this


if I wanted the complete path for each specific folder or specific file to be excluded...

Do you mean that if somewhere in the folder tree here's a file listed in Col E, then delete the entire folder tree?


Edit:

You can change


Const incFilesFolders As Long = 10

to


Const incFilesFolders As Long = 100

That tells Redim Preserve how many more spaces to allocate. I had it set low for testing, but might slow things down

[anmac1789]

if I wanted the complete path for each specific folder or specific file to be excluded...

What I mean by this is if I wanted a specific file path to be excluded such as "C:\test with spaces\subfolder 1\file1.txt" I can change the code from:

For i = LBound(aryFiles) To UBound(aryFiles)
If UCase(p.Name) = UCase(aryFiles(i)) Then Exit Function
Next i
to
For i = LBound(aryFiles) To UBound(aryFiles)
If UCase(p.Path) = UCase(aryFiles(i)) Then Exit Function
Next i
instead of file1.txt being excluded everywhere in every subfolder, by changing the p.Name to p.Path, i can pin-point the exact file path to be excluded


That tells Redim Preserve how many more spaces to allocate. I had it set low for testing, but might slow things down

Const incFilesFolders As Long = 10Do you mean resizing the array by 10 at code execution?

It seems like for long folder/file paths which is 246 characters in length in cell A1, there is a run-time error 9 - subscript out of range occuring and
For i = LBound(aryExcludes) To UBound(aryExcludes) is highlighted after i click debug under the sub "isFolderExcluded"

[Paul_Hossler]

What I mean by this is if I wanted a specific file path to be excluded such as "C:\test with spaces\subfolder 1\file1.txt" I can change the code from:

To exclude a specific file from a specific path, you'd have to do it by hand

As you found, trying the do that with the macro, generates errors.

So you don't want to remove ALL instances of file1.txt, just the one in a specific folder????

It can be done, but seems like a lot more work that it's worth. I'll think about it, but it'd most likely slow things down

[anmac1789]

how nuch slower will it be ?? as long as it doesnt generate errors i think i am fine with waiting a bit...

Can there be two versions ?? one to remove all instances by using its name and another to remove jist one file path pointing to a specific file..??

[Paul_Hossler]

28446

[anmac1789]

It seems like when I search for a path longer than 255 characters, it doesn't list the subfolder contents (deeper nested files or subfolders). The search stops at that point. I looked on this page here: https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 and it says that the character limit in each cell is 32,767 characters.

However, long folder path searching worked in excludes_14 so I am not sure what the difference is between excludes_14 and Excludes_19. Here is an example path length: C:\Users\username\Downloads\something college 2020\f201x sem whatever\Users\whateverusername\Desktop\desktop folders\ALL STUFF\FALL 201x SAMESTER 3 CORSAIR USB 3.0\oct 22, 201x\1yeaF --Hospital Techniques Principles I (Combined) - HOSP200C019_201x18\Content\week 6 in order backup\Math Answer Keys which is 297 characters long which works in Excludes_14 but not in Excludes_19


Option Explicit


Const sPathTop As String = "" 'MAIN PATH GOES HERE WITH \\?\ PREFIX


Const colPath As Long = 1
Const colParent As Long = 2
Const colName As Long = 3
Const colFileFolder As Long = 4
Const colCreated As Long = 5
Const colModified As Long = 6
Const colSize As Long = 7
Const colType As Long = 8


Dim aryExclude As Variant
Dim rowOut As Long
Dim oFSO As Object
Dim wsOut As Worksheet
Dim rPrev As Range


Sub Start()
Dim rowStart As Long
Dim oFile As Object

aryExclude = Array("")


Init


rowStart = rowOut


Call GetFiles(oFSO.GetFolder(sPathTop))

wsOut.Cells(rowStart, colFileFolder).Value = "Parent Folder"

RemoveDups

Cleanup
End Sub


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


If IsExcluded(oPath) Then Exit Sub ' stops recursion

Call ListInfo(oPath, "Subfolder")

For Each oFile In oPath.Files
Call ListInfo(oFile, "File")
Next

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

End Sub


'========================================================================== ==
Private Sub Init()
Dim i As Long

Application.ScreenUpdating = False

If IsArray(aryExclude) Then
For i = LBound(aryExclude) To UBound(aryExclude)
aryExclude(i) = CStr(aryExclude(i))
Next i
End If

Set wsOut = Worksheets("Files")

With wsOut
'get last used row, or 1 if empty
rowOut = .Cells(.Rows.Count, 1).End(xlUp).Row

If rowOut = 1 Then ' blank sheet
.Cells(rowOut, colPath).Value = "FILE/FOLDER PATH"
.Cells(rowOut, colParent).Value = "PARENT FOLDER"
.Cells(rowOut, colName).Value = "FILE/FOLDER NAME"
.Cells(rowOut, colFileFolder).Value = "FILE or FOLDER"
.Cells(rowOut, colCreated).Value = "DATE CREATED"
.Cells(rowOut, colModified).Value = "DATE MODIFIED"
.Cells(rowOut, colSize).Value = "SIZE"
.Cells(rowOut, colType).Value = "TYPE"
End If

rowOut = rowOut + 1

'save the previous data
Set rPrev = wsOut.Cells(1, 1).CurrentRegion
End With

Set oFSO = CreateObject("Scripting.FileSystemObject")
End Sub


Private Sub Cleanup()
wsOut.Columns(colName).HorizontalAlignment = xlLeft
wsOut.Columns(colCreated).NumberFormat = "dddd, mmmm d, yyyy h:mm:ss AM/PM"
wsOut.Columns(colModified).NumberFormat = "dddd, mmmm d, yyyy h:mm:ss AM/PM"
wsOut.Columns(colSize).NumberFormat = "#,##0,.0 ""KB"""

wsOut.Cells(1, 2).CurrentRegion.entireColumnn.AutoFit

Application.ScreenUpdating = True


MsgBox "Done"
End Sub


Private Sub RemoveDups()
wsOut.Cells(1, 1).CurrentRegion.RemoveDuplicates Columns:=1, Header:=xlYes
End Sub


' IFolder object
' Attributes, DateCreated, DateLastAccessed, DateLastModified, Drive,
' Files, IsRootFolder, Name, ParentFolder (IFolder), Path,
' ShortName, ShortPath, Size, SubFolders, Type


' iFile object
' Attributes, DateCreated, DateLastAccessed, DateLastModified, Drive (IDrive),
' Name, ParentFolder (IFolder), Path, ShortName, ShortPath, Size, Type
' Attributes


Private Sub ListInfo(oFolderFile As Object, sType As String)
With oFolderFile
wsOut.Cells(rowOut, colPath).Value = RemovePrefix(.Path)
wsOut.Cells(rowOut, colParent).Value = RemovePrefix(oFSO.GetParentFolderName(.Path)) 'oFSO.GetParentFolderName(.Path) or .ParentFolder.Path
wsOut.Cells(rowOut, colName).Value = .Name
wsOut.Cells(rowOut, colFileFolder).Value = sType
wsOut.Cells(rowOut, colCreated).Value = .DateCreated
wsOut.Cells(rowOut, colModified).Value = .DateLastModified
wsOut.Cells(rowOut, colSize).Value = .size
wsOut.Cells(rowOut, colType).Value = .Type
End With

rowOut = rowOut + 1
End Sub

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

If IsEmpty(aryExclude) Then
IsExcluded = False
Exit Function
End If

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

Private Function RemovePrefix(s As String) As String
If Len(s) < 5 Then
RemovePrefix = s
Else
RemovePrefix = IIf(Left(s, 4) = "\\?\", Right(s, Len(s) - 4), s)
End If
End Function

[Paul_Hossler]

_14 was a long time and many mods ago

I have Long NTFS File names enabled, but Explorer won't let me create a really long one to test



In Main() change the marked line to remove the RemovePrefix( ..... ) and see if it works better



Sub Main()
Dim i As Long

Application.ScreenUpdating = False

Init


ReDim aryFilesFolders(1 To incFilesFolders)
For i = LBound(aryPathsToInclude) To UBound(aryPathsToInclude)
Call getFiles(oFSO.GetFolder(aryPathsToInclude(i)))
Next
ReDim Preserve aryFilesFolders(1 To cntFilesFolders)

listData

Application.ScreenUpdating = True

MsgBox "Done"
End Sub

[anmac1789]

I have used Vice versa from https://www.tgrmn.com/ to create a long folder path and then make another directory with a longer folder path and then combine one folder path with another to make a folder path longer than 255 characters. This is how I was able to make it longer than 255 or 260. I've created a path that is 452 characters in length.

[anmac1789]

So did the longer folder path work with your edition of the workbook?

[anmac1789]

it seems like everyone's on summer vacation lol

[Paul_Hossler]

Sorry, I didn't realize that you wanted me to create a bunch of long file/folder names

I really don't have any reason to

Did the last version work with your LFNs?

[anmac1789]

Sorry, I didn't realize that you wanted me to create a bunch of long file/folder namesI had attached a .zip file called "very long folder path.zip" in post #91 which has a folder path that is longer than 255 characters..


In Main() change the marked line to remove the RemovePrefix( ..... ) and see if it works betterI removed the long prefix and it doesn't work fully. It only lists folders for some reason but not files. Also, it doesn't list the correct number of subfolders, for some reason a few of them are left out. Testing with "very long folder path.zip" will reveal those files that are not listed.

When I was using excludes_14, there was no problem listing subfolders and/or any files. Even excludes_5 was working. There is something in the code which is very different from excludes_14 and excludes_5 im assuming.

[snb]

Have you made any 'progress' in this thread since http://www.vbaexpress.com/forum/showthread.php?68436-Combine-recursive-listing-with-excluded-code&p=407423&viewfull=1#post407423 ?
If only 1 foldername is a problem, you should change that foldername instead of asking in vain for code that is handling this anomaly. I wonder who in this forum is benefitting from the information in this thread ?

[Paul_Hossler]

I had attached a .zip file called "very long folder path.zip" in post #91 which has a folder path that is longer than 255 characters..
There is something in the code which is very different from excludes_14 and excludes_5 im assuming.

I had added some condition flags to avoid unnecessary checks, but apparently didn't cover all the bases

Try 20

[anmac1789]

I'm getting Run-time error 5: invalid procedure call or argument error and this line is highlighted:

i = Application.WorksheetFunction.Match(UCase(p.Name), aryFoldersToExclude, 0) in the sub procedure called isFolderExcluded

[Paul_Hossler]

Works OK for me 0 the 'Files' worksheet completes and has all data that I think it should.

I'm testing with your deeply nested folder tree

Restart the PC and see if that helps

If/when it happens again, see what the .Name of the folder that it's checking is

28514

[anmac1789]

So i restarted my laptop and I re-tested the excel workbook, I am still getting the same error


If/when it happens again, see what the .Name of the folder that it's checking isWhat do you mean by this? your example screenshot is the same as in my workbook..

[Paul_Hossler]

Open the Immediate Window and enter

?p.Name+Enter

The fact that it works on my PC and not on yours makes me wonder if there's something about the data

[anmac1789]

Well what I did was change the drive letter of D into C to suit my computer path that's it. Also, why is there a list in column I, K and M...looking at the code the exclude list was in column C, D and E.


?p.Name+Enterreturns an error run time error 424: object required. It doesn't work for any parent folder regardless where it's located even other parent folders with shorter folder paths

[Paul_Hossler]

what I did was change the drive letter of D into C to suit my computer path

I recall that some user folders can't be in the root of C:





Also, why is there a list in column I, K and M...looking at the code the exclude list was in column C, D and E.

I just moved them out of the way

[anmac1789]

I recall that some user folders can't be in the root of C:The problem is that it doesn't work for any folder even within the Downloads folder

[Paul_Hossler]

Can you attach the failing workbook?

Are you using the same 'very long folder path.zip' from post 91?

I used

\\?\c:\Users\Daddy\Downloads\very long folder path

without problems

[anmac1789]

I am getting the same error message again. I downloaded the exact same workbook that you have attached in the previous post.


Are you using the same 'very long folder path.zip' from post 91?I am also using the same zip file and testing it out on that and also on smaller folders on my computer and still i am getting the same runtime error 5 msg

28522
28523

[Paul_Hossler]

1. For now, remove the UCase( ) around p.path and see if the error goes away


2. Try this also

https://newtonexcelbach.com/2014/03/08/problems-with-ucase-and-lcase-in-vba/


So mosey on over to Tools > References, and look for any library prefixed with “MISSING”.

That’s the real culprit.


If this doesn’t fix the problem, then close Excel, clean out the temp directory, clean out %appdata%\Roaming\Microsoft\Forms, then restart Excel. If that doesn’t help you probably need to repair/reinstall Office.

[anmac1789]

1. For now, remove the UCase( ) around p.path and see if the error goes awayI've removed that from isFolderExcluded sub, isFileExcluded sub and isSpecificFileExcluded sub. Still getting the same error

[Paul_Hossler]

Well ...

put the UCase (..) back and try the second suggestions

If that doesn't work, then I'm really out of ideas since it works here.

[anmac1789]

I've tried pre-fixing vba before Ucase and it still doesn't work just like the website suggests.. so im guessing im going to re-install office. Because I was hit with a bad virus yesterday and I was scanning for 6 hrs and it turned up like 105 trojans and alot of files were affected in the temp folder. So im going to try that now.

[anmac1789]

So i re-installed excel 365 and tried to run Excludes_20.xlsm again but still I am getting run time error 5. I was looking on this website: https://www.mrexcel.com/board/threads/runtime-error-5-invalid-procedure-call-or-argument.825523/

it says
the worksheet function Match requires a range object as the second argument, not an arrayCould this have anything to do with it ?

[snb]

Sub M_snb()
sn = Split("That is not correct")
MsgBox Application.Match("not", sn, 0)
End Sub


Sub M_snb()
MsgBox Application.Match("not", Split("this is not correct"), 0)
End Sub

[anmac1789]

Sub M_snb()
sn = Split("That is not correct")
MsgBox Application.Match("not", sn, 0)
End SubBy itself, doesn't work because I have Option Explicit. But if I add Dim sn As Variant then the sub works. The second one also works without having to define split with dim

[Paul_Hossler]

So i re-installed excel 365 and tried to run Excludes_20.xlsm again but still I am getting run time error 5. I was looking on this website: https://www.mrexcel.com/board/threads/runtime-error-5-invalid-procedure-call-or-argument.825523/

it says Could this have anything to do with it ?


Doubt it

Match works with arrays and it files an excluded folder

28529

[anmac1789]

which directory path are you testing this on ? I have done ?isarray(aryFoldersToExclude) and it returns false the error message. I also tested aryFoldersToExclude in the watch window and the value returned "out of context" and type - "empty".

So I've tested this on another computer and it seems to work. It seems that even after re-installing excel the code on my laptop doesn't work. What is going on here....

Also, it seems that in the sub isSpecificFileExcluded and isFolderExcluded the arguement inside UCase() should be changed to p.Path not p.name because paths are not being excluded properly, they were only excluding subfolder name and filename

here is the problem workbook so you can see for yourself if there are any changes compared to your workbook

[Paul_Hossler]

The way it (is intended) to work is below

It does seem to do what it's supposed to

1. If isFolderExcluded(oPath) returns True, then the recursion down that folder path stops

2. If isFileExcluded(oFile) or isSpecificFileExcluded(oFile) return True, then the file is not added






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


If isFolderExcluded(oPath) Then Exit Sub ' stops recursion

Call addFileFolder(oPath)

For Each oFile In oPath.Files
If Not isFileExcluded(oFile) Then
If Not isSpecificFileExcluded(oFile) Then Call addFileFolder(oFile)

Else
If Not isSpecificFileExcluded(oFile) Then Call addFileFolder(oFile)
End If
Next

For Each oSubFolder In oPath.SubFolders
Call getFiles(oSubFolder)
Next
End Sub





The way you described what you wanted to do, the 2 'File' excludes were based on the file .Name so I think that it's correct


because paths are not being excluded properly, they were only excluding subfolder name and filename

I was excluding folders from 'hell yeah' and below (ver 20a)

[Paul_Hossler]

which directory path are you testing this on ? I have done ?isarray(aryFoldersToExclude) and it returns false the error message. I also tested aryFoldersToExclude in the watch window and the value returned "out of context" and type - "empty".

So I've tested this on another computer and it seems to work. It seems that even after re-installing excel the code on my laptop doesn't work. What is going on here....

Also, it seems that in the sub isSpecificFileExcluded and isFolderExcluded the arguement inside UCase() should be changed to p.Path not p.name because paths are not being excluded properly, they were only excluding subfolder name and filename

here is the problem workbook so you can see for yourself if there are any changes compared to your workbook


Correcting / changing the two marked lines and entering



A1 = \\?\c:\users\daddy\downloads\very long folder path
C1 = hell yeah

seems to work








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

i = -1
On Error Resume Next
i = Application.WorksheetFunction.Match(UCase(p.Name), aryFoldersToExclude, 0) ' <<<<<<<<<<<<<<<<<<<<<<<<<
On Error GoTo 0


isFolderExcluded = (i <> -1)
End Function


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

i = -1
On Error Resume Next
i = Application.WorksheetFunction.Match(UCase(p.Name), aryFilenamesToExclude, 0)
On Error GoTo 0


isFileExcluded = (i <> -1)


End Function


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

i = -1
On Error Resume Next
i = Application.WorksheetFunction.Match(UCase(p.Name), arySpecificFilesToExclude, 0) ' <<<<<<<<<<<<<<<<<<<<<<
On Error GoTo 0


isSpecificFileExcluded = (i <> -1)


End Function

[anmac1789]

I dont know why the above code was re-written I did not see any change. It is the same exact code in Excludes_20a and I also did a test just to list a parent folder and all it's files without any excludes, I am still getting run-time error 5: invalid procedure call or argument on


i = Application.WorksheetFunction.Match(UCase(p.Name), aryFoldersToExclude, 0)

[Paul_Hossler]

Again, it works for me

One thing to check ...

In the VBE go to Tools, Options, and make sure "Unhandled Errors" is selected


28533

Also I added a check to fail softly if there was no top level folder specified

In your 20 (Problem file) it was blank and generated an ugly error

[anmac1789]

In the VBE go to Tools, Options, and make sure "Unhandled Errors" is selectedOk so, it seems like now it searches. What does "break on unhandled errors" mean? does it mean that it's ignoring run time error 5 and just forcing itself to proceed to complete the code?

[Paul_Hossler]

The On Error Resume Next handles the error generated if the Match() fails, so it doesn't stop





i = -1
On Error Resume Next
i = Application.WorksheetFunction.Match(UCase(p.Name), aryFoldersToExclude, 0) ' <<<<<<<<<<<<<<<<<<<<<<<<<
On Error GoTo 0

isFolderExcluded = (i <> -1)




Basically it says the

1. Make i = -1 (I use that as a check value)

2. If there's any errors just keep on going

3. i = index of p.name in aryFoldersToExclude or an error if not found (but continue since Resume Next 'statement')

4. Turn off the 'Resume Next'

5. If i <> -1 (my check value) then the folder was in the list and IsFolderExcluded = True
5. If i = -1 (my check value) then the folder was NOT in the list and IsFolderExcluded = False

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/on-error-statement

When there's a possibilty of getting an error, I typically will do it that way

[anmac1789]

Okay it seems like I'm just going to live with the fact that I have to turn on "break on unhandled errors" hopefully there is an answer as to why this error message occurs. Another issue is that for each parent path, in column D it still says Folder instead of parent folder, and then all subfolders inside the parent directory should say subfolder

[Paul_Hossler]

Okay it seems like I'm just going to live with the fact that I have to turn on "break on unhandled errors" hopefully there is an answer as to why this error message occurs.

Sorry



28535

[anmac1789]

Can something be done to remove the "\\?" after pressing the button 1 so that the search returns a clean path ?

[Paul_Hossler]

Add the marked line to sub listData





'final format
wsOut.Columns(colPath).Replace What:="\\?\", Replacement:=vbNullString, LookAt:=xlPart
wsOut.Columns(colName).HorizontalAlignment = xlLeft
wsOut.Columns(colCreated).NumberFormat = "m/dd/yyyy"
wsOut.Columns(colModified).NumberFormat = "m/dd/yyyy"
wsOut.Columns(colSize).NumberFormat = "#,##0"