Sorting array of file names alphabetically

**mimino** · 10-11-2013, 10:20 AM

Hi, all!

I am troubleshooting a macro (for a co-worker) that looks at a folder full of presentations and then copies all of the slides from each of those files into the main presentation file (the one the macro is ran from). The issues is, for whatever reason, the macro has not been properly sorting the files this week.

For example, file name beginning with SB16A_00_XX comes before SB16_00_XX

I've tried incorporating some quicksort routines, but i still sorts files like the above. We're trying to make it sort files like it does in the windows explorer "Name" button.

Any tips are appreciated!

here's the macro code:

Sub InsertAllSlides()
Dim osource As Presentation
Dim otarget As Presentation
Dim oSlide As Slide
Dim bMasterShapes As Boolean
Dim vArray() As String
Dim x As Long
' Insert all slides from all presentations in the same folder as this one
' INTO this one; do not attempt to insert THIS file into itself, though.

Set otarget = ActivePresentation
' Change "*.PPT" to "*.PPTX" or whatever if necessary:
EnumerateFiles ActivePresentation.Path & "\", "*.PPT", vArray

On Error GoTo ErrorHandler

With ActivePresentation
For x = 1 To UBound(vArray)
If Len(vArray(x)) > 0 Then


Set osource = Presentations.Open(vArray(x), , , False)



For Each oSlide In osource.Slides
oSlide.Copy

Next oSlide
osource.Close
Set osource = Nothing


End If
Next
End With

ErrorHandler:
Debug.Print Err.Number, Err.Description

End Sub

Sub EnumerateFiles(ByVal sDirectory As String, _
ByVal sFileSpec As String, _
ByRef vArray As Variant)
' collect all files matching the file spec into vArray, an array of strings

Dim sTemp As String
ReDim vArray(1 To 1)

sTemp = Dir$(sDirectory & sFileSpec)
Do While Len(sTemp) > 0
' NOT the "mother ship" ... current presentation
If sTemp <> ActivePresentation.Name Then
ReDim Preserve vArray(1 To UBound(vArray) + 1)
vArray(UBound(vArray)) = sDirectory & sTemp
End If
sTemp = Dir$
Loop

End Sub

**Norie** · 10-11-2013, 10:26 AM

Where are you sorting the filenames?

**mimino** · 10-11-2013, 10:58 AM

Nowhere at this point. It "sorts" the files via the EnumerateFiles routine in the vArray.

I did add the quicksort function to this macro and had it call out the names of the files, but they were in the same exact order as before.

**Norie** · 10-11-2013, 11:02 AM

EnumeratFiles doesn't 'sort' the files, it only fills the array using Dir.

Dir doesn't return files in any particular order.

What code did you used for quicksort?

**mimino** · 10-11-2013, 11:22 AM

Originally Posted by Norie

EnumeratFiles doesn't 'sort' the files, it only fills the array using Dir.

Dir doesn't return files in any particular order.

What code did you used for quicksort?

See below:

Private Sub QuickSort(strArray(), intBottom As Integer, intTop As Integer)
Dim strPivot As String, strTemp As String
Dim intBottomTemp As Integer, intTopTemp As Integer
intBottomTemp = intBottom
intTopTemp = intTop
strPivot = strArray((intBottom + intTop) \ 2)
While (intBottomTemp <= intTopTemp)
While (strArray(intBottomTemp) < strPivot And intBottomTemp < intTop)
intBottomTemp = intBottomTemp + 1
Wend
While (strPivot < strArray(intTopTemp) And intTopTemp > intBottom)
intTopTemp = intTopTemp - 1
Wend
If intBottomTemp < intTopTemp Then
strTemp = strArray(intBottomTemp)
strArray(intBottomTemp) = strArray(intTopTemp)
strArray(intTopTemp) = strTemp
End If
If intBottomTemp <= intTopTemp Then
intBottomTemp = intBottomTemp + 1
intTopTemp = intTopTemp - 1
End If
Wend
'the function calls itself until everything is in good order
If (intBottom < intTopTemp) Then QuickSort strArray, intBottom, intTopTemp
If (intBottomTemp < intTop) Then QuickSort strArray, intBottomTemp, intTop
End Sub

End Sub

**Norie** · 10-11-2013, 11:48 AM

Based on the ASCII character set, SB16A_00_XX does come before SB16_00_XX.

Do you want to sort on the characters before the first _?

**mimino** · 10-11-2013, 12:15 PM

Originally Posted by Norie

Based on the ASCII character set, SB16A_00_XX does come before SB16_00_XX.

Do you want to sort on the characters before the first _?

Sure

It's above my "coding" levels at this point...

**Norie** · 10-11-2013, 12:44 PM

I've adapted your quicksort code to only look at the characters before the first '_'.

It works for your small sample of data.

Private Sub QuickSort(strArray(), intBottom As Integer, intTop As Integer)
Dim strPivot As String, strTemp As String
Dim intBottomTemp As Integer, intTopTemp As Integer

    intBottomTemp = intBottom
    intTopTemp = intTop
    
    strPivot = Split(strArray((intBottom + intTop) \ 2), "_")(0)
    
    While (intBottomTemp <= intTopTemp)
        While (Split(strArray(intBottomTemp), "_")(0) < strPivot And intBottomTemp < intTop)
            intBottomTemp = intBottomTemp + 1
        Wend
        While (strPivot < Split(strArray(intTopTemp), "_")(0) And intTopTemp > intBottom)
            intTopTemp = intTopTemp - 1
        Wend
        If intBottomTemp < intTopTemp Then
            strTemp = strArray(intBottomTemp)
            strArray(intBottomTemp) = strArray(intTopTemp)
            strArray(intTopTemp) = strTemp
        End If
        If intBottomTemp <= intTopTemp Then
            intBottomTemp = intBottomTemp + 1
            intTopTemp = intTopTemp - 1
        End If
    Wend
    'the function calls itself until everything is in good order
    If (intBottom < intTopTemp) Then QuickSort strArray, intBottom, intTopTemp
    If (intBottomTemp < intTop) Then QuickSort strArray, intBottomTemp, intTop
    
End Sub

Note this will error if any of the filenames doesn't have a '_' in it.

**mimino** · 10-11-2013, 02:00 PM

Originally Posted by Norie

I've adapted your quicksort code to only look at the characters before the first '_'.

It works for your small sample of data.

Private Sub QuickSort(strArray(), intBottom As Integer, intTop As Integer)
Dim strPivot As String, strTemp As String
Dim intBottomTemp As Integer, intTopTemp As Integer

    intBottomTemp = intBottom
    intTopTemp = intTop
    
    strPivot = Split(strArray((intBottom + intTop) \ 2), "_")(0)
    
    While (intBottomTemp <= intTopTemp)
        While (Split(strArray(intBottomTemp), "_")(0) < strPivot And intBottomTemp < intTop)
            intBottomTemp = intBottomTemp + 1
        Wend
        While (strPivot < Split(strArray(intTopTemp), "_")(0) And intTopTemp > intBottom)
            intTopTemp = intTopTemp - 1
        Wend
        If intBottomTemp < intTopTemp Then
            strTemp = strArray(intBottomTemp)
            strArray(intBottomTemp) = strArray(intTopTemp)
            strArray(intTopTemp) = strTemp
        End If
        If intBottomTemp <= intTopTemp Then
            intBottomTemp = intBottomTemp + 1
            intTopTemp = intTopTemp - 1
        End If
    Wend
    'the function calls itself until everything is in good order
    If (intBottom < intTopTemp) Then QuickSort strArray, intBottom, intTopTemp
    If (intBottomTemp < intTop) Then QuickSort strArray, intBottomTemp, intTop
    
End Sub

Note this will error if any of the filenames doesn't have a '_' in it.

Thanks! It did erorr out on this line, however:

While (Split(strArray(intBottomTemp), "_")(0) < strPivot And intBottomTemp < intTop)

I guess I should have elaborated a little more on this. These files start with "SB1x_", or "SB1xA(B)_", and after the first "_" are followed by a number in the "0x_" format and then followed by some text.
I.E. "SB16_07_Someword"
Any chance to adapt the code for that condition? I've noticed that the original code works perfectly fine if I insert another underscore after "SB16" and make it "SB16_A_", then it works as it should.

If that's too much of a request on the code side, I'll just tell my co-worker to insert some underscores into those files. I'm just perplexed that the original code worked fine until this week. Something must've changed, but I have no idea what.

Thanks!

edit: found out why it errored out. The first file in the array is left blank, since it's the main presentation file. I've added the resume on error statement, since this error is fixed down the line in the first sub. If you know of a better way to fix this, please post

Many thanks!

**mimino** · 10-16-2013, 10:09 AM

Oddly enough, it ran just fine this week w/o any code manipulation. The only difference that I could see was all the presentations were saved as ".ppt" this time.

Sorting array of file names alphabetically

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Sorting array of file names alphabetically

Re: Sorting array of file names alphabetically

Re: Sorting array of file names alphabetically

Re: Sorting array of file names alphabetically

Re: Sorting array of file names alphabetically

Re: Sorting array of file names alphabetically

Re: Sorting array of file names alphabetically

Re: Sorting array of file names alphabetically

Re: Sorting array of file names alphabetically

Re: Sorting array of file names alphabetically

Thread Information

Users Browsing this Thread

Similar Threads

sorting unique cells alphabetically using array

Create array of file names/sheet names

Automatic Sorting Alphabetically in an Array

Sorting file names

[SOLVED] sorting names alphabetically when names may start with numbers

Bookmarks

Bookmarks

Posting Permissions