Sorting array of file names alphabetically

[mimino]

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:

Please Login or Register  to view this content.

[Norie]

Where are you sorting the filenames?

[mimino]

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]

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]

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]

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]

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]

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

It works for your small sample of data.

Please Login or Register  to view this content.

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

[mimino]

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

It works for your small sample of data.

Please Login or Register  to view this content.

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]

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.