Looping through files in order with Dir()

Question

I am attempting to insert several pictures into an excel spreadsheet, and the save it as a PDF. I have been able to figure out how to space the pictures and iterate through all the pictures in a folder, but I can't seem to figure out how to iterate through the pictures in order.

I have found that I can iterate through the .jpg files in a specific folder using Dir as seen in this question: Loop through files in a folder using VBA? and this question macro - open all files in a folder. It has worked wonders, but I need to iterate through the pictures in order. The pictures are labeled "PHOTOMICS0" with that final number increasing.

Here is what I am working with.

counter = 1
MyFile = Dir(MyFolder & "\*.jpg")
Do While MyFile <> vbNullString
    incr = 43 * counter
    Cells(incr, 1).Activate
    ws1.Pictures.Insert(MyFolder & "\" & MyFile).Select
    MyFile = Dir
    counter = counter + 1
Loop

So far, MyFile has gone from "PHOTOMICS0" to "PHOTOMICS4", 9, 10, 7, 2, 3, 8, 6, 5, and finally 1. When repeated it follows the same order. How can I increment through these in numerical order?

Define "in order". Do you mean sorted ascending or descending by: file size, name, date created, date saved, etc... — cybernetic.nomad, Jun 28 '19 at 19:33
going from "PHOTOMICS0" to "PHOTOMICS10" in this case, or whatever is the highest number. So ascending I suppose. — B. Moore, Jun 28 '19 at 19:34
You best bet may be to load all the filenames in an array and then [sort them](https://stackoverflow.com/questions/152319/vba-array-sort-function) before opening each one. — cybernetic.nomad, Jun 28 '19 at 19:48
That's a great idea. I don't know why I never thought of that. I'll give it a try and see what happens. — B. Moore, Jun 28 '19 at 19:57
https://stackoverflow.com/questions/4282940/does-dir-make-any-guarantee-on-the-order-of-files-returned. OP has put the solution in the question. ;) — Siddharth Rout, Jun 28 '19 at 20:56

B. Moore · Accepted Answer · 2019-06-30T18:19:06.213

Thanks to the advice of cybernetic.nomad and Siddharth Rout I was able to fix this.

I used some functions and lines of codes from these posts:

How to find numbers from a string?

How to sort an array of strings containing numbers

Here is the functioning code:

counter = 0
MyFile = Dir(MyFolder & "\*.jpg")
Do While MyFile <> vbNullString
    ReDim Preserve PMArray(counter)
    PMArray(counter) = MyFile
    MyFile = Dir
    counter = counter + 1
Loop

Call BubbleSort(PMArray)

b = counter - 1
For j = 0 To b
    a = j + 1
    If i > 24 Then a = j + 2
    incr = 43 * a
    Cells(incr, 1).Activate
    ws1.Pictures.Insert(MyFolder & "\" & PMArray(j)).Select
Next j

Where BubbleSort and the associated function used in BubbleSort are:

Sub BubbleSort(arr)
  Dim strTemp As String
  Dim i As Long
  Dim j As Long
  Dim lngMin As Long
  Dim lngMax As Long
  lngMin = LBound(arr)
  lngMax = UBound(arr)
  For i = lngMin To lngMax - 1
    For j = i + 1 To lngMax
      If onlyDigits(arr(i)) > onlyDigits(arr(j)) Then
        strTemp = arr(i)
        arr(i) = arr(j)
        arr(j) = strTemp
      End If
    Next j
  Next i
End Sub

Function onlyDigits(s) As Integer
    ' Variables needed (remember to use "option explicit").   '
    Dim retval As String    ' This is the return string.      '
    Dim retvalint As Integer
    Dim i As Integer        ' Counter for character position. '

    ' Initialise return string to empty                       '
    retval = ""

    ' For every character in input string, copy digits to     '
    '   return string.                                        '
    For i = 1 To Len(s)
        If Mid(s, i, 1) >= "0" And Mid(s, i, 1) <= "9" Then
            retval = retval + Mid(s, i, 1)
        End If
    Next

    ' Then return the return string.                          '
    retvalint = CInt(retval)
    onlyDigits = retvalint
End Function

Looping through files in order with Dir()

1 Answers1