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

Question

PixPath is the full path to the jpg folder, and I use the following code 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 that the files are returned in alphabetical order, and not in the order specified in the folder. Is there any way around this?

Answer 1

You cannot do this with Dir .

An alternative is:

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

• The final array X contains 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 

  Learn more about using FileSystemObject here .

Answer 2

The answer from brettdj (thank-you brettdj) works well and probably the method I will use, but I found something else that also works, and this may give an advantage in other situations. Firstly, it retains the extreme ease of using Dir to scroll 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 alphanumeric in Col A. Then I use Custom List to sort Col A with (fake) digital sorting so that I can process jpg in sequential order. Then clear Col A and repeat the next folder.

To create a custom list:

in row 1 of the working column, enter

  =ROW() & ".jpg" 

and Fill anything you want. In my case, I used 1000 elements in my user list, because the maximum number of jpg that I expect in any folder.

User lists accept only text (or "plain text" in accordance with MS Help), so the newly created list of formulas must be converted to text using Insert> Special> Values ​​before importing as a user list. Each item in the list is one of the expected file names. The final user list is as follows:

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

After importing a new user list (Tools> Options> User Lists> Import), it becomes available in the drop-down menu in the menu "Data"> "Sort"> "Parameters"> "Order of sorting the first key".

If you are doing this sort with VBA, then this is what Recorder writes:

 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 a new custom list. Remember to change this to OrderCustom: = False when doing regular sorting. User lists remain attached to the Wkb that they created until deleted.