Strange files indexes when using "For Each"

Question

I am using "For Each" statement to browse through all the files in a folder, extract their names and some content and place them in some tables. the only problem I am facing is that the files are processed in a random sequence.

The files in the folder have the names (which corresponds to the time they have been added to the folder): 203909_20160910_1149.csv 203909_20160910_1739.csv 203909_20160911_1259.csv

But "For Each" command processes them in an strange order: first 203909_20160910_1739.csv then 203909_20160911_1259.csv last 203909_20160910_1149.csv

All the tutorials I've seen so far are saying that "For Each" is always using fixed sequence of files ("first to last"), and there is no way I can change it. So I am trying to figure out how are the files indexed if not by name and not by date? Are there any attributes I could set to force A->Z sequence?

Are you using the `Scripting.FileSystemObject` or some other method? — Comintern, Oct 07 '16 at 15:28

score 0 · Answer 1 · edited May 23 '17 at 12:33

0

You can use the code below, credit to @Frédéric Hamidi From question:

Does Dir() make any guarantee on the order of files returned?

Dim allFiles As Variant
allFiles = GetFileList(MyDir & "wp*.xls")
If IsArray(allFiles) Then
    Call QuickSort(allFiles, LBound(allFiles), UBound(allFiles))
End If

Dim x As Integer
Dim lstFile As String
x = 1

' still need to loop through results to get lastFile
While lstFile <> LastFileName 
    lstFile = allFiles(x)
    x = x + 1
Wend

For i = x To UBound(allFiles)
    MyFileName = allFiles(i)
    Cells(LastRow + 1, 1) = MyFileName
    LastRow = LastRow + 1
Next i

edited May 23 '17 at 12:33

Community

1
1

answered Oct 07 '16 at 12:38

Preston

7,399
8
54
84

thank you for your answer. I was hoping there was some kind of logic in the way files are sorted. But as far as I see now, I cannot use ForEach :( – Ania Golieva Oct 10 '16 at 08:11

Strange files indexes when using "For Each"

1 Answers1