Use Dir to return files from a folder in file system order

Question

PixPath is the full path to a folder of jpg's, and I'm using code like the following to process each jpg.

fileName = Dir(PixPath)
Do Until fileName = ""
   If Right$(fileName, 4) = ".jpg" Then   
      fileName = Dir()

      <process this jpg>

   End If
Loop

This works fine except the files are returned in alpha-numeric order, and not the order that the files are shown listed in the folder. Is there any way around this?

Answer 1

You can't do this with Dir.

An alternative is to:

• Use FileSystemObject to access all the files in your directory
• Read in all the .jpg files into an array X
• Use Val to compare the .jpgs by value to sort in a numeric ascending order

• The final array X contains the sorted files

  Sub Test()
  Dim objFSO As Object
  Dim objFolder As Object
  Dim objFiles As Object
  Dim objFile As Object
  Dim X
  Dim lngFileCnt As Long
  Dim lngCnt As Long
  Dim i As Long
  Dim j As Long
  Dim strBuffer1 As String
  Dim strFolder As String
  
  Set objFSO = CreateObject("Scripting.fileSystemObject")
  strFolder = "C:\temp"
  Set objFolder = objFSO.getFolder(strFolder)
  Set objFiles = objFolder.Files
  lngFileCnt = objFiles.Count
  ReDim X(1 To lngFileCnt)
  
  'grab all jpg files        
  For Each objFile In objFiles
      If Right$(objFile.Name, 3) = "jpg" Then
          lngCnt = lngCnt + 1
          X(lngCnt) = objFile.Name
      End If
  Next
  
  'resize array to number of jpg files
  ReDim Preserve X(1 To lngCnt)
  
  'sort array by numeric value
  For i = 1 To lngCnt
      For j = (i + 1) To lngCnt
          If Val(X(i)) > Val(X(j)) Then
              strBuffer1 = X(j)
              X(j) = X(i)
              X(i) = strBuffer1
          End If
      Next
  Next
  MsgBox Join(X, ";")
  End Sub
  

  See here for more info on using FileSystemObject.

Answer 2

The answer from brettdj (thank-you brettdj) works well and is probably the method I'll use, but I've found something else that also works and that might offer an advantage in other situations. For one thing, it retains the extreme simplicity of using Dir to loop through folders.

In Excel 11, I use Dir (as described in the question) to create a list of jpg files from each folder (one folder at a time), sorted alpha-numeric in Col A. Then I use a Custom List to sort Col A with a (fake) numeric sort so I can process my jpg's in serial order. Then clear Col A, and repeat with the next folder.

To generate the Custom List:

in Row 1 of a work column enter

 =ROW() & ".jpg"

and Fill Down to whatever suits. In my case I used 1000 items on my Custom List because that's the maximum number of jpg's I expect in any folder.

Custom Lists take only text (or "simple text" according to MS Help) so the newly generated list of formulas has to be converted to text using Paste>Special>Values before importing as a Custom List. Each item on the list is one of the expected file names. The final Custom List looks like this:

 1.jpg
 2.jpg
 3.jpg
 …
 …
 1000.jpg

After I import my new Custom List (Tools>Options>Custom Lists>Import), it becomes an available selection in the dropdown menu at Data>Sort>Options>First Key Sort Order.

If you're doing this sort with VBA then here's what the Recorder provides:

Range("A:A").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=6, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

The first 5 Custom Lists are built into Excel, so OrderCustom:=6 is the new Custom List. Remember to change this back to OrderCustom:=False when doing normal Sorting. Custom Lists stay attached to the Wkb they're created in until deleted.