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
with
. Please let me know if I'm doing it right or wrong (still a vba n00b).
Attachment 27999