This questions is an extension from this thread: http://www.vbaexpress.com/forum/show...ths-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....