Directory sequence 2

[GShen]

Hi,
I found some code in vb that someone put in a form which populates a listbox using a function for the rowsource. It is using the dir(path,vbdirectory) command. Is there any way to get this to come out in descending sequence? It doesn't look like there is any other parameters. Also, I see logic in there parsing a field called CODE. What is the makeup of this string ? What does it do ?

Any help at all would be appreciated.

Thanks

Here is the function.

' Purpose: To read the contents of a directory into a ListBox.
' Usage: Create a ListBox. Set its RowSourceType to "DirListBox"
' Parameters: The arguments are provided by Access itself.
' Notes: You could read a FileSpec from an underlying form.
' Error handling not shown. More than 512 files not handled.

Static StrFolders(0 To 511) As String
Dim StrPath As String
Dim StrDirName As String

StrPath = "U:\CompanyDocuments\Circulars\Fine Fare\"
'StrPath = "C:\"

StrDirName = Dir(StrPath, vbDirectory)

Select Case code
Case 0 ' Initialize
DirFineFareFolders = True
Case 1 ' Open: load file names into array
DirFineFareFolders = Timer
IntCount = 0 ' Add this to reset so doesn't duplicate file list.

Do While Len(StrDirName) > 0
If GetAttr(StrPath & StrDirName) = vbDirectory Then
If StrDirName <> "." And StrDirName <> ".." Then
StrFolders(IntCount) = StrDirName
IntCount = IntCount + 1
End If
'IntCount = IntCount + 1
End If

StrDirName = Dir

Loop

Case 3 ' Rows
DirFineFareFolders = IntCount
Case 4 ' Columns
DirFineFareFolders = 1
Case 5 ' Column width in twips
DirFineFareFolders = 1440
Case 6 ' Supply data
DirFineFareFolders = StrFolders(row)
Case 9 ' End
End Select


Remember when... everything worked and there was a reason for it?

 

[SkipVought]

Collect the DIR entries into an array in your loop.

Sort the array.

Load the control from the array in a loop.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[GShen]

Skip,
It looks like it is in a array called StrFolders.

How do I sort it ?

Remember when... everything worked and there was a reason for it?

 

[SkipVought]

Easiest way is to write into a sheet and use Excel Sort.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[PHV]

come out in descending sequence
A brute force starting point:

...
  Loop
  For N = 0 To IntCount - 2
    For M = N To IntCount - 1
      If StrFolders(N) < StrFolders(M) Then
        tmp = StrFolders(M)
        StrFolders(M) = StrFolders(N)
        StrFolders(N) = tmp
      End If
    Next
  Next
Case 3                          ' Rows
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GShen]

Okay everyone I will try the internal sort 1st because opening up excel is just something else I need to read and understand. I can't believe their isn't a easier way of doing this but I will have to live with it. Do either one of you know what the case statement is parsing the field CODE for? Where does CODE get loaded and what is the string makeup? I was thinking maybe one the CODES for used for sorting sequence.
Would make sense but then again......



Remember when... everything worked and there was a reason for it?

 

[SkipVought]

"I found some code in vb that someone put in a form..."

Ask someone. It is SPECIFIC.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[strongm]

>Where does CODE get loaded

Your example is for a user-function to populate a listbox. The Code variable therefore gets generated automatically by Access itself. And no, it doesn't generate one for sort order (even if it did, since it is automatically generated you couldn't choose the order requested yourself ...)

>Excel ... I can't believe their isn't a easier way of doing this

There is, using a disconnected recordset, which is really only a minor modification to your code:

[blue]Option Compare Database
Option Explicit

' Requires reference to Microsoft ActiveX Data Objects (latest version you've got)
' Your listbox should have it's RecordSourceType set to the name of this function: Elephant
' All the parameters are automatically provided by Access
Public Function Elephant(fld As Control, id As Variant, row As Variant, col As Variant, code As Variant) As Variant
    Static rs As ADODB.Recordset
    Dim strPath As String
    Dim strDirName As String
    
    strPath = "c:\program files\" ' Example uses Program Files folder

    Select Case code
        Case acLBInitialize            ' Initialize.
            Elephant = True
        Case acLBOpen                    ' Open.
            Elephant = Timer        ' Unique ID.
            
            ' OK, replace original array solution with a disconnected recordset
            ' which we can sort. Keep as much of the OPs loop as possible so that
            ' difference can be seen
            Set rs = New ADODB.Recordset
            With rs
                .Fields.Append "File", adVarChar, 255
                .Open
                strDirName = Dir(strPath, vbDirectory)
                Do While Len(strDirName) > 0
                    If GetAttr(strPath & strDirName) = vbDirectory Then
                        If strDirName <> "." And strDirName <> ".." Then
                            .AddNew "File", strDirName
                        End If
                    End If
                strDirName = Dir
                Loop
                .Sort = "File" ' or .Sort = "File DESC" for descending sort order
            End With
        Case acLBGetRowCount            ' Get rows.
            Elephant = rs.RecordCount
        Case acLBGetColumnCount    ' Get columns.
            Elephant = 1
        Case acLBGetColumnWidth    ' Get column width.
            Elephant = -1            ' Use default width.
        Case acLBGetValue
            ' Get the data
            rs.Move row, adBookmarkFirst
            Elephant = rs("File").Value
    End Select
End Function[/blue]

 

[GShen]

StrongM,
Ok. Makes sense about the field CODE. I don't do much with functions. Obviously I will have to change the PATH as you noted and obviously I can set ELEPHANT back to my original FUNCTION NAME,.. correct ? Also, where all the references to "FILE", do I leave that all alone ?

Thanks.

Remember when... everything worked and there was a reason for it?

 

[strongm]

>all the references to "FILE", do I leave that all alone ?

Completely up to you. 'File' is just a placeholder name for the one column in the disconnected recordset. You can change it to something more meaningful for your purpose if you want, such as 'Folder'

 

[GShen]

StrongM,

You the MAN!! Works like a charm. I am not exactly sure what all that code is doing but I really don't care at this point. Not only that the old procedure took about 3 seconds to populate and about 5-10 seconds to scroll down with a bunch of blank records at the end. Your code loads in a blink, no blank records and it can be asc or desc. Since I am loading it by date, I put the DESC sequence in. PERFECT!!! Exactly what I wanted.

Thanks!

Remember when... everything worked and there was a reason for it?