I am trying to clean up some existing code
Sheets("Control").Select
MyDir = Cells(2, 1)
CopySheet = Cells(6, 2)
MyFileName = Dir(MyDir & "wp*.xls")
' when the loop breaks, we know that any subsequent call to Dir implies
' that the file need to be added to the list
While MyFileName <> LastFileName
MyFileName = Dir
Wend
MyFileName = Dir
While MyFileName <> ""
Cells(LastRow + 1, 1) = MyFileName
LastRow = LastRow + 1
MyFileName = Dir
Wend
My question relates to how Dir
returns results and if there are any guarantees on the order of results. When using Dir
in a loop as above, the code implies that the resultant calls to Dir
are ordered by name.
Unless Dir
guarantees this, it's a bug which needs to be fixed. The question, does Dir() make any guarantee on the order in which files are returned or is it implicit?
Based on @Frederic's answer, this is the solution I came up with.
Using this quicksort algorithm in conjunction and a function that returns all files in a folder ...
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
Dir returns the first file name that matches pathname. To get any additional file names that match pathname, call Dir again with no arguments. When no more file names match, Dir returns a zero-length string ("").
DIR is a very special function in VBA, its job is to return a string representing the name of a file, directory, or archive that matches a specified pattern. DIR function only returns the first file name or folder name from a location that matches the specified attributes.
There's no guarantee that Dir()
will return the files in any particular order. The MS Access VBA documentation even says:
Tip Because file names are retrieved in no particular order, you may want to store returned file names in an
array
, and then sort the array.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With