this only works for 1st level directories, not deeper nested subfolder names and not even deeper nested subfolder paths!
Posts by anmac1789
-
-
Does anyone want to answer this question? Does anyone have a clue where to begin? I have been waiting for so long to get a reply but nothing...the last thread I made got responses but this threat it seems everyone just left the forum and does not want to answer even though I made a detailed post. Does anyone want to respond to this or should I make a new thread?
This forum seems dead
-
Anyone have an idea about this ??
-
-
This post is in continuation from excluding folder paths with spaces
My next question is how to :
1. exclude 1st level subfolder(s) & deeper nested subfolder(s) (2nd..3rd..4th) that are still listed (see image attached of excel worksheet). I would like to exclude everything inside including the excluded folder itself from the list, not just the contents inside that subfolder....
2. Recursive listing of parent paths...list to be continued below as each parent folder is added and searched
If other deeper nested subfolders have the same name within the 2nd, 3rd, 4th subfolder level then in that case, would a folder path would make sense to exclude instead of names?
-
i see, okay i will make a new thread
-
in addition to post #6 - it seems like the problem now is for longer parent folder paths...the files do not even show up..the path length is "C:\Users\[username]\Downloads\duracell 4GB"
-
so basically, your saying that I just need to add a "\" to the parent path ?? Also, what if i want to get all subfolders within a parent directory but I want to exclude a deeper nested subfolder ? how can I adjust the code?
-
I think the problem is occurring with "asf() = Split(Replace(excludedSubfolders, ", ", ","), ",")"
-
it almost works except for one thing. I have attached the same format except its for a different folder for which it doesn't work for, Basically, it shouldn't show any list because there is no file under the "a" folder. I have adjusted the code to take into your substitutions. Please take a look:
updated code:
Code
Display MoreOption Explicit 'http://www.ozgrid.com/forum/showthread.php?t=158478 Dim iRow As Long Sub ListFiles() Dim lRow As Long iRow = 11 lRow = Range("B" & Rows.Count).End(xlUp).Row If lRow >= iRow Then Range("B" & iRow & ":E" & Range("B" & Rows.Count).End(xlUp).Row).Clear End If Call ListMyFiles(Range("A1"), Range("A2"), Range("A3")) Application.GoTo Range("B3"), True End Sub Sub ListMyFiles(mySourcePath As String, IncludeSubfolders As String, _ Optional excludedSubfolders As String = "") Dim myObject As Scripting.FileSystemObject Dim mySource As Scripting.Folder, myFile As Variant Dim iCol As Integer Dim mySubFolder As Scripting.Folder, v As Variant Dim asf() As String, sf As String asf() = Split(Replace(excludedSubfolders, ", ", ","), ",") Set myObject = New Scripting.FileSystemObject Set mySource = myObject.GetFolder(mySourcePath) On Error Resume Next For Each myFile In mySource.Files iCol = 1 Cells(iRow, iCol).Value = myFile.Path 'iCol = iCol + 1 'Cells(iRow, iCol).Value = myFile.Name iRow = iRow + 1 Next 'For Each mySubFolder In mySource.subfolders 'Cells(iRow, iCol).Value = mySubFolder.Path 'iCol = iCol + 1 'Cells(iRow, iCol).Value = mySubFolder.Name 'iRow = iRow + 1 'Next If IncludeSubfolders Then For Each mySubFolder In mySource.SubFolders If excludedSubfolders = "" Then Call ListMyFiles(mySubFolder.Path, False) Else sf = Trim(Right(mySubFolder.Path, Len(mySubFolder.Path) - Len(mySourcePath))) If IndexStrArray(asf(), sf) = -1 Then Call ListMyFiles(mySubFolder.Path, True) End If End If Next End If End Sub 'val is not case sensitive Function IndexStrArray(vArray() As String, sVal As String) As Long Dim v As Variant, i As Long On Error GoTo Minus1 For i = 0 To UBound(vArray) If LCase(vArray(i)) = LCase(sVal) Then IndexStrArray = i Exit Function End If Next i Minus1: IndexStrArray = -1 End Function
-
Hello, this post is in response to the thread posted here:
and also here: excluding folder paths - Excel General - OzGrid Free Excel/VBA Help Forum
My question is that this code does not take into account excluding folder paths with spaces. For example, searching path "C:\KINGSTON 240GB SSD" and lets say I have a subfolder called "Documents" inside that parent folder. If I am typing "Documents" (without quotes) in cell A3 and click "list files in subfolders less exception list" then I get a list of all files within "Documents" folder. Which is not what this code is supposed to do. How can I fix this ?
As proof here is my code and a screenshot of the folder structure:
Code
Display MoreOption Explicit 'http://www.ozgrid.com/forum/showthread.php?t=158478 Dim iRow As Long Sub ListFiles() Dim lRow As Long iRow = 11 lRow = Range("B" & Rows.Count).End(xlUp).Row If lRow >= iRow Then Range("B" & iRow & ":E" & Range("B" & Rows.Count).End(xlUp).Row).Clear End If Call ListMyFiles(Range("A1"), Range("A2"), Range("A3")) Application.GoTo Range("B3"), True End Sub Sub ListMyFiles(mySourcePath As String, IncludeSubfolders As String, _ Optional excludedSubfolders As String = "") Dim myObject As Scripting.FileSystemObject Dim mySource As Scripting.Folder, myFile As Variant Dim iCol As Integer Dim mySubFolder As Scripting.Folder, v As Variant Dim asf() As String, sf As String asf() = Split(Replace(excludedSubfolders, ", ", ","), ",") Set myObject = New Scripting.FileSystemObject Set mySource = myObject.GetFolder(mySourcePath) On Error Resume Next For Each myFile In mySource.Files iCol = 1 Cells(iRow, iCol).Value = myFile.Path 'iCol = iCol + 1 'Cells(iRow, iCol).Value = myFile.Name iRow = iRow + 1 Next 'For Each mySubFolder In mySource.subfolders 'Cells(iRow, iCol).Value = mySubFolder.Path 'iCol = iCol + 1 'Cells(iRow, iCol).Value = mySubFolder.Name 'iRow = iRow + 1 'Next If IncludeSubfolders Then For Each mySubFolder In mySource.SubFolders If excludedSubfolders = "" Then Call ListMyFiles(mySubFolder.Path, False) Else sf = LCase(Right(mySubFolder.Path, Len(mySubFolder.Path) - Len(mySourcePath) - 1)) If IndexStrArray(asf(), sf) = -1 Then Call ListMyFiles(mySubFolder.Path, True) End If End If Next End If End Sub 'val is not case sensitive Function IndexStrArray(vArray() As String, sVal As String) As Long Dim v As Variant, i As Long On Error GoTo Minus1 For i = 0 To UBound(vArray) If vArray(i) = sVal Then IndexStrArray = i Exit Function End If Next i Minus1: IndexStrArray = -1 End Function
-
does anyone have an idea about this?
-
my original question was regarding this post: List files macro, need to add exclude specific subfolder in it
First, It seems like when I tested this macro, it doesn't seem to work for folder names with spaces in them for example "C:\test 1"
Next, it only seems to exclude top level directory folders, not deeper nested folders within the parent directory. How can this macro be adjusted to exclude deeper nested subfolders?
Next, how can I make this macro display folder paths in addition to the file paths including the path of the parent folder?
Next, when excluding a folder with no files in it, the other file paths in different subfolders get duplicated. How can this be fixed also? it seems like alot is wrong with this code that I wish to extend functionality for.
I am attaching 2 pieces of code that I used in seperate workbooks so you can see what functionality I want added
Code: this is the primary worksheet with information I want
Display MoreOption Explicit Sub SomeSub() Call GetFiles("\\?\C:\test") 'attach "\\?\" at the beginning for long folder path names! ex..'GetFiles("\\?\INSERT..." 'can also list multiple "Call GetFiles("\\?\[insert new folder path here]")" to list multiple folder paths all at once End Sub Sub GetFiles(ByVal path As String) Dim fso As Object Set fso = CreateObject("Scripting.FileSystemObject") Dim folder As Object Set folder = fso.GetFolder(path) Dim subfolder As Object Dim file As Object For Each subfolder In folder.SubFolders GetFiles (subfolder.path) Next subfolder Range("A1") = "parent folder" Range("A1").Offset(0, 1) = "FILE/FOLDER PATH" Range("A1").Offset(0, 2) = "FILE or FOLDER" Range("A1").Offset(0, 3) = "DATE CREATED" Range("A1").Offset(0, 4) = "DATE MODIFIED" Range("A1").Offset(0, 5) = "SIZE" Range("A1").Offset(0, 6) = "TYPE" Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Replace(folder, "\\?\", "") 'Range("A" & Rows.Count).End(xlUp).Offset(0, 1) = Replace(folder, "\\?\", "") 'Range("A" & Rows.Count).End(xlUp).Offset(0, 2) = folder.Name Range("A" & Rows.Count).End(xlUp).Offset(0, 3) = "FOLDER" Range("A" & Rows.Count).End(xlUp).Offset(0, 4) = folder.datecreated Range("A" & Rows.Count).End(xlUp).Offset(0, 5) = folder.DateLastModified For Each subfolder In folder.SubFolders 'Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Replace(subfolder.path, "\\?\", "") 'Range("A" & Rows.Count).End(xlUp).Offset(0, 1) = Replace(folder, "\\?\", "") 'Range("A" & Rows.Count).End(xlUp).Offset(0, 2) = subfolder.Name 'Range("A" & Rows.Count).End(xlUp).Offset(0, 3) = "FOLDER" 'Range("A" & Rows.Count).End(xlUp).Offset(0, 4) = subfolder.datecreated 'Range("A" & Rows.Count).End(xlUp).Offset(0, 5) = subfolder.DateLastModified Next subfolder For Each file In folder.Files Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Replace(file.path, "\\?\", "") 'Range("A" & Rows.Count).End(xlUp).Offset(0, 1) = Replace(folder, "\\?\", "") 'Range("A" & Rows.Count).End(xlUp).Offset(0, 2) = file.Name Range("A" & Rows.Count).End(xlUp).Offset(0, 3) = "FILE" Range("A" & Rows.Count).End(xlUp).Offset(0, 4) = file.datecreated Range("A" & Rows.Count).End(xlUp).Offset(0, 5) = file.DateLastModified Range("A" & Rows.Count).End(xlUp).Offset(0, 6) = file.Size Range("A" & Rows.Count).End(xlUp).Offset(0, 7) = file.Type Next file With Range("E:F") .NumberFormat = "dddd mmmm dd, yyyy H:mm:ss AM/PM" 'long file date and time End With Set fso = Nothing Set folder = Nothing Set subfolder = Nothing Set file = Nothing End Sub
Code: this is for the excluding folder paths I want fixed and incorporated into the main excel worksheet
Display MoreOption Explicit 'http://www.ozgrid.com/forum/showthread.php?t=158478 Dim iRow As Long Sub ListFiles() Dim lRow As Long iRow = 11 lRow = Range("B" & Rows.Count).End(xlUp).Row If lRow >= iRow Then Range("B" & iRow & ":E" & Range("B" & Rows.Count).End(xlUp).Row).Clear End If Call ListMyFiles(Range("A1"), Range("A2"), Range("A3")) Application.GoTo Range("B3"), True End Sub Sub ListMyFiles(mySourcePath As String, IncludeSubfolders As String, _ Optional excludedSubfolders As String = " ") Dim myObject As Scripting.FileSystemObject Dim mySource As Scripting.Folder, myFile As Variant Dim iCol As Integer Dim mySubFolder As Scripting.Folder, v As Variant Dim asf() As String, sf As String asf() = Split(Replace(excludedSubfolders, ", ", ","), ",") Set myObject = New Scripting.FileSystemObject Set mySource = myObject.GetFolder(mySourcePath) On Error Resume Next For Each myFile In mySource.Files iCol = 1 Cells(iRow, iCol).Value = myFile.Path 'iCol = iCol + 1 'Cells(iRow, iCol).Value = myFile.Name iRow = iRow + 1 Next 'For Each mySubFolder In mySource.subfolders 'Cells(iRow, iCol).Value = mySubFolder.Path 'iCol = iCol + 1 'Cells(iRow, iCol).Value = mySubFolder.Name 'iRow = iRow + 1 'Next If IncludeSubfolders Then For Each mySubFolder In mySource.SubFolders If excludedSubfolders = "" Then Call ListMyFiles(mySubFolder.Path, True) Call ListMyFiles(mySource.Path, False) Else sf = LCase(Right(mySubFolder.Path, Len(mySubFolder.Path) - Len(mySourcePath) - 1)) If IndexStrArray(asf(), sf) = -1 Then Call ListMyFiles(mySubFolder.Path, True) Call ListMyFiles(mySource.Path, False) End If End If Next End If End Sub 'val is not case sensitive Function IndexStrArray(vArray() As String, sVal As String) As Long Dim v As Variant, i As Long On Error GoTo Minus1 For i = 0 To UBound(vArray) If vArray(i) = sVal Then IndexStrArray = i Exit Function End If Next i Minus1: IndexStrArray = -1 End Function
-
yes but I am not fully understanding the real idea here... i can grasp whats happening but i am not sure what it really means from the above threat
-
Hello, this post is in response to here: List files macro, need to add exclude specific subfolder in it
What does the above line mean? and also,
Code
Display MoreFunction IndexStrArray(vArray() As String, sVal As String) As Long Dim v As Variant, i As Long On Error GoTo Minus1 For i = 0 To UBound(vArray) If vArray(i) = sVal Then IndexStrArray = i Exit Function End If Next i Minus1: IndexStrArray = -1 End Function
What exactly is the above code doing here?