Function To List Directories in Alphabetical Order 2

[dpimental]

I am using the standard method to retrieve directories.
MyName = Dir(MyPath, vbDirectory) 'Retrieve 1st Entry
and
MyName = Dir ' Retrieve Next Entry

How do I get the directories into alphabetical order.
I don't want to have to write them to a table and sort them
in ascending order.

I really don't want to push them to an array and then sort the array in ascii ascending order?

So is there any way to use the standard method but to apply some filter on it, or make it get the directories in alphabetical order.

What I am doing, is passing a path to a function that will search for subdirectories, open the directory and import any excel spreadsheets to a table, and then go on to the next excel file, then the next subdirectory, then the next directory and start all over again?

Any ideas out there?

 

[danvlas]

I don't want to have to write them to a table and sort them
in ascending order.

I really don't want to push them to an array and then sort the array in ascii ascending order

No kidding?

Anyway, if all you do is search and import, I don't think you need to sort anything.
Check thread705-333994 and modify the code to meet your needs.

I just hope you don't say "I don't want to revise the code, just need a turn-key solution"

Dan

 

[oharab]

Here's a function to sort an array. I'm sure you can work out the rest!

B

Sub QuickSort(A() As Integer, ByVal Low As Integer, ByVal Hi As Integer)
'
' Very fast sort: n Log n comparisons
'
' Calling convention:
' Redim A(1 To 20) as Integer
' QuickSort A(), 1, 20
'
Dim MidValue As Integer, i As Integer, j As Integer, Temp As Integer
If Hi <= Low Then Exit Sub
MidValue = A((Low + Hi) \ 2)
i = Low
j = Hi
Do While i <= j
If A(i) >= MidValue And A(j) <= MidValue Then
Temp = A(i)
A(i) = A(j)
A(j) = Temp
i = i + 1
j = j - 1
Else
If A(i) < MidValue Then i = i + 1
If A(j) > MidValue Then j = j - 1
End If
Loop
QuickSort A(), Low, j
QuickSort A(), i, Hi
End Sub
----------------------------------------
Ben O'Hara
Home: bpo@SickOfSpam.RobotParade.co.uk
Work: bo104@SickOfSpam.westyorkshire.pnn.police.uk
(in case you've not worked it out get rid of Sick Of Spam to mail me!)
Web:

http://www.RobotParade.co.uk

----------------------------------------

 

[dpimental]

I'm sorry, I have been spending so much time with project managers and overseers and directors (i.e. non technical people), that I have forgotten the real use of SARCASM!

Thank you all for the reminder?

I will take a look at the sort function.

 

[FancyPrairie]

Here's another way of doing it. It dumps the results to a recordset and then resorts the recordset.

    Dim rst As ADODB.Recordset
    Dim strName As String
    
    strName = Dir("C:\My Documents\*.*")
    
    If (strName <> vbNullString) Then

        Set rst = New ADODB.Recordset
        rst.Fields.Append &quot;FileName&quot;, adVarChar, 50
        rst.Open
    
        While strName <> vbNullString
            rst.AddNew
            rst!FileName = strName
            strName = Dir
        Wend
    
        rst.Sort = &quot;FileName&quot;

'*****************************************************
'*  Data has now been collected and sorted.          *
'*  This section of code simply prints out the list  *
'*****************************************************

        rst.MoveFirst
    
        While Not rst.EOF
            Debug.Print rst!FileName
            rst.MoveNext
        Wend

    End If

 

[danvlas]

Have a star from me for this one FancyPrairie! This seems to solve my problem of 'array to recordset'.
And it also means that I have to put an eye on ADO

Dan

 

[dpimental]

Thanks FancyPrairie.

That did the trick.

Nice and Neat.