For/Next instead of For Each with FileItem In SourceFolder.Files

[scottcolbury]

Hi all,
I need to use a For/Next loop instead of "For Each FileItem In SourceFolder.Files" when looking at files in folder.

Instead of:
For Each FileItem In SourceFolder.Files
next FileItem

I need to use:
tempFileIncrement = 1
iTotal = SourceFolder.Files.Count
For q = tempFileIncrement To iTotal
Next q

Also, if this is possible what is the syntax for retrieving the name of a file?
I imagine its something like "FileItem(q).Name"?

Any ideas?
Thanks in advance!
s_c

 

[John_w]

AKAIK you can't loop through the Folder.Files collection using a loop index, because the key argument in Folder.Files(key) should be a file name, not an index number. Instead, put the file names in an array using a For Each loop and then use each array element to access the Folder.Files collection.

Sub FSO_Files_Loop()

    Dim FSO As Object
    Dim SourceFolder As Object
    Dim FileItem As Object
    Dim i As Long
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set SourceFolder = FSO.GetFolder("C:\Windows")
    
    'Create array containing file names
    
    Dim FileNamesArray() As String
    ReDim FileNamesArray(1 To SourceFolder.Files.Count)
    i = 0
    For Each FileItem In SourceFolder.Files
        i = i + 1
        FileNamesArray(i) = FileItem.Name
    Next
    
    'Now loop through Files collection using each file name array element as the key value
    
    For i = 1 To SourceFolder.Files.Count
        Debug.Print i, SourceFolder.Files(CStr(FileNamesArray(i))).Name
    Next
        
End Sub

 

[scottcolbury]

Thanks, that worked.
Any ideas for speeding up how slow For/Each is when reading the names of files in a folder with a large number of files?
s_c

 

[John_w]

You could run the DOS DIR command to create the array. This assumes the DIR command returns the same number of files as Files.Count.

Sub FSO_Files_Loop2()

    Dim FSO As Object
    Dim SourceFolder As Object
    Dim fileNamesArray As Variant
    Dim i As Long
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set SourceFolder = FSO.GetFolder("C:\Windows")
    
    fileNamesArray = Split(CreateObject("WScript.Shell").Exec("cmd /C DIR /B /ON /A-D """ & SourceFolder.Path).StdOut.ReadAll, vbCrLf)
    Debug.Assert UBound(fileNamesArray) = SourceFolder.Files.Count

    For i = 0 To SourceFolder.Files.Count - 1
        Debug.Print i + 1, SourceFolder.Files(CStr(fileNamesArray(i))).Name
    Next
        
End Sub