15

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?

Solution

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
Community
  • 1
  • 1
Ahmad
  • 22,657
  • 9
  • 52
  • 84

2 Answers2

11

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.

Frédéric Hamidi
  • 258,201
  • 41
  • 486
  • 479
  • Ha, the MSDN docs don't have that gem of a Tip...Thanks. Am I correct than in saying that the bug does exist with the existing code – Ahmad Nov 26 '10 at 06:43
  • @Ahmad, if the files are supposed to be ordered in the spreadsheet then yes, that's a bug. – Frédéric Hamidi Nov 26 '10 at 06:44
  • No the sheets AFAIK the result doesn't need to be ordered, however the above code does try to locate all files not in the spreadsheet and assumes that files in the source folder are ordered. The files are dated (`yymmdd`) which is handled by the wildcard part of the initial `Dir` call – Ahmad Nov 26 '10 at 06:51
  • @Ahmad, I see, that's the `While MyFileName <> LastFileName` part. Indeed, you probably shouldn't do that. – Frédéric Hamidi Nov 26 '10 at 06:55
  • I am working on translating a function from VisualBasic to C#. This function uses the function Dir$(strPath); What would be the equivalent in C#, keeping in mind that Dir$(strPath) retrieves the file list with an order given by the filesystem itself ? It could be System.IO.GetFiles (string path) ? the documentation at https://docs.microsoft.com/en-us/dotnet/api/system.io.directory.getfiles?redirectedfrom=MSDN&view=netframework-4.7.2#overloads, says "The order of the returned file names is not guaranteed", so could we assume it behaves exactly like the Dir$() function from VisualBasic ? – Roger Feb 13 '19 at 10:49
0

I know this post is old but I share the solution I have found for those who are also looking for a short solution.

I write all the filenames in a Excel sheet column and I use a variable which will get the name of the files. Then I run a loop to open each file based on the name retrieved by the variable according to the order they have written in the column.

For Row_Value = 1 To 10 
    NameFile= Range("N" & Row_Value).Value 'NameFile = "Worbook1"
    MyFile = Dir("C\Desktop\Folder1\" & NameFile & ".xlsm")
Next Row_Value

I hope it's clear.

Lionel
  • 11
  • 2