Application.GetOpenFilename file processing order?

[Thebatfink]

Hi,

I have a sub which does various processing on data gathered from other workbooks. The workbooks are inputted from the user via Application.GetOpenFilename. I was curious if it is possible to process these files in a specific order, Alphabetic or Numeric, rather than just in the order the files were selected in the file dialog?

Basically my filenames are prefixed with a date, in a 6 digit number format with no hypens or slashes and I really need to process them in this order?

Is it possible to order the filenames gathered in the filename variable?

Thanks!
Batfink

 

[Richard Schollar]

AFAIK you'd need to copy across to an array and sort the array (eg using a bubblesort routine) - search the board for these because you should find several examples.

 

[Ruddles]

If you used MultiSelect:=True in your .GetOpenFileName, the filenames will already be in an array, so as Richard says, a simple bubble sort will do the job.

 

[Thebatfink]

OK I had a search and I've hacked this together. I think it works but I'm not sure how to check the contents of the array?

Is this sorting the filenames how I want?

fn = Application.GetOpenFilename("Excel-files,*.xlsm", 1, _
"Select One Or More Files To Open", , True)
    
    If TypeName(fn) = "Boolean" Then Exit Sub
      
    First = LBound(fn)
    Last = UBound(fn)
    For i = First To Last - 1
        For j = i + 1 To Last
            If fn(i) > fn(j) Then
                Temp = fn(j)
                fn(j) = fn(i)
                fn(i) = Temp
            End If
        Next j
    Next i

Cheers!

 

[Ruddles]

That's a bubble sort, ascending order. To display the contents of the array, use this:-

    Debug.Print "---"
    For i = First To Last
       Debug.Print i, fn(i)
    Next i

Insert that code once before the For i and again after the Next i statements in your existing code. It will print the elements of the array in the Immediate window (Ctrl-G) before and after the sort has taken place.

 

[Thebatfink]

Thanks!

Alittle off the topic but if I wanted to gather two sets of filenames (different folders) using two prompts.. how could I join the fn arrays together? Is that possible?

 

[Ruddles]

Not really off topic - it's a valid follow-up question, I think.

Assuming your arrays are called fn and fn2, this code extends the upper bound of fn and copies the contents of fn2 into the newly-created space:-

ReDim Preserve fn(1 To UBound(fn) + UBound(fn2))
 
For aPtr = LBound(fn2) To UBound(fn2)
  fn(UBound(fn) - UBound(fn2) + aPtr) = fn2(aPtr)
Next aPtr

Here's a complete testframe which you can use to test the code:-

[FONT=Courier New]Option Explicit[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]Public Sub TestFrame()[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]  Dim fn As Variant
  Dim fn2 As Variant
  Dim aPtr As Integer
  
  fn = Application.GetOpenFilename(MultiSelect:=True)
  fn2 = Application.GetOpenFilename(MultiSelect:=True)
  
  Debug.Print "---old fn---": For aPtr = LBound(fn) To UBound(fn): Debug.Print aPtr, fn(aPtr): Next aPtr
  Debug.Print "---fn2---": For aPtr = LBound(fn2) To UBound(fn2): Debug.Print aPtr, fn2(aPtr): Next aPtr
  
[/FONT][FONT=Courier New][COLOR=green]  ' this is the bit which joins the arrays
[/COLOR]  
  ReDim Preserve fn(1 To UBound(fn) + UBound(fn2))
  
  For aPtr = LBound(fn2) To UBound(fn2)
  fn(UBound(fn) - UBound(fn2) + aPtr) = fn2(aPtr)
  Next aPtr
  
 [/FONT][FONT=Courier New][COLOR=green] ' --------------------------------------
[/COLOR]  
  Debug.Print "---new fn---": For aPtr = LBound(fn) To UBound(fn): Debug.Print aPtr, fn(aPtr): Next aPtr[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]End Sub
[/FONT]

 

[Ruddles]

You may have to make up your mind whether to sort the arrays before you merge them or afterwards, depending on whether you want the follow-on processing to process the files in filename order within folder, or by filename regardless of folder.

Note that the filename held in the array includes the full path to the file, so if you sort the merged array it will end up in folder order, then filename order within folder. (Am I explaining this clearly?)

To modify your bubble sort code to sort the array into filename order regardless of folder name, change:-

If fn(i) > fn(j) Then

to:-

If Mid(fn(i), InStrRev(fn(i), "\") + 1) > Mid(fn(j), InStrRev(fn(j), "\") + 1) Then

This just compares the filename - everything after the last "\" (found by InStrRev).