Sort Order

[ddrew]

I have a combobox (cbo_FileNames) that is unbound and gets fed on activate from a folder

Set SourceFolder = FSO.GetFolder("c:\GundogManager\BackUp\")

My problem is that the file names are as follows:

GundogManagerBackUp 02_12_13.xls
GundogManagerBackUp 03_12_13.xls
GundogManagerBackUp 07_08_13.xls
GundogManagerBackUp 16_11_13.xls
GundogManagerBackUp 17_12_13.xls

As you can see it consists of the name and a date of the backup. When it populates the combobox I want it to go in date order I newest to oldest. At the moment its going of the first number. Is there a wy to correct this please?

 

[JHB]

I would first put the file names in a table, (then you can sort it), and set the combobox row source to the table.
An array is also a possibility.

 

[Mihail]

I would first put the file names in a table, (then you can sort it), and set the combobox row source to the table.
An array is also a possibility.

I think that the sort order will not work because the date format (dd_mm_yy)

@ddrew
See the attachment for a possible solution.
I used a very simple algorithm to sort the list but should be enough faster for your amount of data.

If you can, change the backup file name: FileName YYYY_MM_DD
This will get you a more than one benefit:
1) You will see the sorted files in Windows Explorer;
2) You can use JHB's solution;
3) You have no more need of ChangeDatePart function in my example.

Good luck !

 

[JHB]

Of cause, the date part of the file name goes into a separate field, (formatted in proper date format).
I didn't mention it, because I thought it was obvious, sorry about that!

 

[ddrew]

Thanks for the replys but how do a populate a table from filenames that are held in a folder on my PC?

 

[Mihail]

Show us (me) the entire code that populate your combo.
This will prevent us (me) to reinvent the wheel in order to teach you how to populate the table.

 

[ddrew]

Show us (me) the entire code that populate your combo.
This will prevent us (me) to reinvent the wheel in order to teach you how to populate the table.

As requested, thanks.

Private Sub Form_Activate()

    Dim FSO As Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder
    Dim FileItem As Scripting.File
    Dim r As Long
    
    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder("c:\GundogManager\BackUp\")

    For Each FileItem In SourceFolder.Files
        Me.cbo_FileNames.AddItem (FileItem.Name)
    Next FileItem

End Sub

 

[Mihail]

Inspired from:
1) http://www.utteraccess.com/wiki/index.php/Recordsets_for_Beginners#Adding_a_Record
2) http://en.wikipedia.org/wiki/Date_format_by_country

 

[namliam]

changing your backup scheme to YYYYMMDD or YYYY-MM-DD or atleast remember to do this in the future IS the best way to solve this, for now and in the future....

Also, any filename that carries a date.... YYYYMMDD

 

[Mihail]

It known benificial for sortordered.
Constant Summary:
ASCENDING
Enumeration value indicating the items are sorted in increasing order.
DESCENDING
Enumeration value indicating the items are sorted in decreasing order.
UNSORTED
Enumeration value indicating the items are unordered.
Method details
Public static SortOrder[] values()
Returns an array containing the constants of this enum type, in the order they are declared. This method may be used to iterate over the constants as follows:
for (SortOrder c : SortOrder.values())
System.out.println(c);
Returns:
An array containing the constants of this enema type, in the order they are declared

))))))))))))
I know the benefit of aircraft.
However I can't fly on my own.