List all folders and sub folders except those that begin with... Assistance requested

[aarcher]

Here is the code i found and adapted to my needs and works great. But I want to add an exception, to skip folders starting with ???? and move on to the next folder. Any assistance would be appreciated. So i want: For Each myfile, except.

Sub ListMyFiles(MySourcePath, includesubfolders)
Set MyObject = CreateObject("Scripting.FileSystemObject")
Set mysource = MyObject.GetFolder(MySourcePath)
On Error Resume Next
For Each myfile In mysource.Files
icol = 1
Cells(IRow, icol).Value = myfile.Name
icol = icol + 1
Cells(IRow, icol).Value = myfile.Path
icol = icol + 1
Cells(IRow, icol).Value = myfile.DateCreated
icol = icol + 1
Cells(IRow, icol).Value = myfile.datelastmodified
icol = icol + 1
Cells(IRow, icol).Value = myfile.Size
'icol = icol + 1



IRow = IRow + 1
Next
End Sub


Thanks for any assistance,

Al

 

[rlv01]

But I want to add an exception, to skip folders starting with ????

Can you clarify what you mean by folders starting with "????", since the "?" character is a wildcard character in Windows and thus an illegal character for file and folder names.

 

[aarcher]

Except folders starting with "R:\zzzz" or "R:\aaaa" i do not want to list files within those folders, I have 233,000 file names to list and excluding those folders would save me about 50,000.

 

[Mentor82]

Except folders starting with "R:\zzzz" or "R:\aaaa" i do not want to list files within those folders, I have 233,000 file names to list and excluding those folders would save me about 50,000.

Hi,
Add the following IF statement before loop FOR and run the FOR only if the IF stayement is FALSE

IF mysource.Name like "aaaa*" or mysource.Name like "zzzz*" then
'go next folder
Else
      'Run loop For...
      For each....
      Next
End if

 

[aarcher]

Hmm i don't seem to be getting the subfolders, i thought i was, any ideas.

 

[rlv01]

Hmm i don't seem to be getting the subfolders, i thought i was, any ideas.

Mentor beat me to it. Because your Sub ListMyFiles is coded so that it does not look at subfolders (Notice that your function parameter includesubfolders not used anywhere within the subroutine,) excluding "R:\zzzz" or "R:\aaaa" does not seem that helpful. You said you adapted this code from something else. Could it be that you took too much out?

 

[aarcher]

Hmm, i bet i did, where should i add "includesubfolders"?

 

[Mentor82]

Hi,
I thought you had a problem with skipping particular folders naming only. To get subfolders you need to have two procedures:
1 procedure to get a main folder and its files. At the end of these procedure yiu need to call the subprocedure for the subfolders
2 procedure for the sub folders - it's pretty much the same as procedure for folders - it needs one parameter the path and within the procedure you list all the files included first and then you find another folder and call the subfolder procedure in recurrence. I can provide you with the proper code tomorrow earliestif thats ok for you.
Regards
Sebastian

 

[aarcher]

Yes, i would really appreciate your assistance. Thanks so much!!

 

[rlv01]

A couple of points.

1. This could take a while if you have a huge number of files and subfolders.
2. There are non-recursive ways to accomplish this as well.
3. How to list files in subfolders is a question that a million people have asked before you, so there is a lot of information available via search, both here and via google.

Sub ListMyFiles(MySourcePath)
    Dim MyObject As Object, MySource As Object, MySubFolder As Object, MyFile As Object
    Dim iRow As Long, iCol As Long
    '
    Set MyObject = CreateObject("Scripting.FileSystemObject")
    Set MySource = MyObject.GetFolder(MySourcePath)
    'On Error Resume Next

    iRow = 1
    iCol = 1
    ListFiles MyObject, MySource, iRow, iCol          'Top level
End Sub

Private Sub ListFiles(ByRef MyObject, ByRef MySource, ByRef iRow As Long, ByRef iCol As Long)
    Dim MySubFolder As Object, MyFile As Object

    On Error Resume Next
    For Each MySubFolder In MySource.subFolders
        ListFiles MyObject, MySubFolder, iRow, iCol   'recursively look in subdirectories
    Next MySubFolder

    For Each MyFile In MySource.Files
        'Except files in folders starting with "R:\zzzz" or "R:\aaaa"
        Select Case UCase(Left(MyFile.Path, 7))
        Case "R:\ZZZZ", "R:\AAAA"
        Case Else
            iCol = 1
            Cells(iRow, iCol).Value = MyFile.Name
            iCol = iCol + 1
            Cells(iRow, iCol).Value = MyFile.Path
            iCol = iCol + 1
            Cells(iRow, iCol).Value = MyFile.DateCreated
            iCol = iCol + 1
            Cells(iRow, iCol).Value = MyFile.datelastmodified
            iCol = iCol + 1
            Cells(iRow, iCol).Value = MyFile.Size
            'icol = icol + 1
            iRow = iRow + 1
        End Select
    Next MyFile
End Sub