excluding folder paths

anmac1789 · Jan 10th 2021

Hello, this post is in response to here: List files macro, need to add exclude specific subfolder in it

Code

excludedSubfolders As String = ""

What does the above line mean? and also,

Code

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

Display More

What exactly is the above code doing here?

saurabhjaina211 · Jan 10th 2021

excludedSubfolders As String = ""

This is Variable declaration syntax in VBA . Here excludedSubfolders is a String type variable and current value is null string.

The function defined is checking the index number of string in the given array.

anmac1789 · Jan 10th 2021

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

saurabhjaina211 · Jan 11th 2021

Hi,

Not sure what your question is.

But, If you create a new function as below where we are calling the function and pass the parameters, it will search the string "dog" in array myanimals in the function and return the index/ position of substring.

Code

Sub callFunc()
    Dim returnVal As Long
    Dim myanimals(3) As String

    myanimals(0) = "cat"
    myanimals(1) = "dog"
    myanimals(2) = "elephant"
    
    'calling function and passing array and string to check
    returnVal = IndexStrArray(myanimals, "dog")
    MsgBox returnVal
End Sub

Display More

Thanks,

Saurabh

anmac1789 · Jan 17th 2021

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

Option 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

Display More

Code: this is for the excluding folder paths I want fixed and incorporated into the main excel worksheet

Option 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

Display More

anmac1789 · Jan 18th 2021

does anyone have an idea about this?

excluding folder paths

Participate now!

Share