List Of Files as per the Date created

[baha17]

Dear All,

I have a user form with a listbox. On userform initialize event, I have a code that display all the .mdb file in the listbox. But the problem is the items are sorted alphabetically by the month name. Is that possible to list the file as per the date created from the latest to the oldest?
Thanks for the help
Baha


Here is my code:

Private Sub UserForm_Initialize()
AllocationOB = True
ListBoxStartUp
End Sub
Sub ListBoxStartUp()
    Dim fileList() As String
    Dim fName As String
    Dim fPath As String
    Dim i As Integer
    fPath = "P:\Everyone\For Baha\Gaming Common\Sands_VirtualCP" & "\BackUpDataFiles\"
    fName = Dir(fPath & "*.mdb")
    While fName <> ""
        i = i + 1
        ReDim Preserve fileList(1 To i)
        fileList(i) = fName
         'get next filename
        fName = Dir()
    Wend
     'see if any files were found
    If i = 0 Then
        MsgBox "No files found"
        Exit Sub
    End If
     'cycle through the list and add to listbox
    For i = 1 To UBound(fileList)
        Me.FilesListBox.AddItem fileList(i)
    Next
End Sub

 

[RickXL]

Hi.

Here is one suggestion. Instead of a 1D array use a 2D one. Then you will be able to add the date to the filename data. This 2D array can then be sorted into date order.

One way to do this is to use a "SortedList". It is a 2D array that automatically sorts itself into order as new data is added. So no special sorting step is required..

I don't know the format of your file names so I have just assumed that the DateValue function will convert them into true dates. You will need to change that if that is not the case. The true date is used as the key for the SortedList and the filename is entered as a value.

Sub ListBoxStartUp()

    Dim fileList As Object
    Set fileList = CreateObject("System.Collections.SortedList")
    Dim fName As String
    Dim fDate As Date
    Dim fPath As String
    Dim i As Integer
    
    fPath = "P:\Everyone\For Baha\Gaming Common\Sands_VirtualCP" & "\BackUpDataFiles\"
    fName = Dir(fPath & "*.mdb")

    While fName <> ""
        fDate = DateValue(Left(fName, Len(fName) - 4))
        fileList.Item(fDate) = fName
         'get next filename
        fName = Dir()
    Wend
     'see if any files were found
    If fileList.Count = 0 Then
        MsgBox "No files found"
        Exit Sub
    End If
     'cycle through the list and add to listbox
    For i = 0 To fileList.Count - 1
        Me.FilesListBox.AddItem fileList.GetByIndex(i)
    Next
End Sub

Googling:
systems collections sortedlist
will throw up lots of info about SortedList.

 

[baha17]

Hi Rick,
First of all thank you for your input.
The date was in "MMM-DD-YYYY" format therefore I followed your suggestion to change the date format. But still get Run-Time error:Type Mismatch

Cheers

 

[RickXL]

Hi.

Obviously I can't do any tests with your data so I have made some up. The file names I use are now:

Apr-01-2015.ddd
Jan-01-2015.ddd
Mar-01-2014.ddd

When I try it, the extension is removed OK and DateValue converts the dates OK. So for me it is working as expected. All I can suggest it try printing out the values of fName inside the While loop. For example:

Sub ListBoxStartUp()

    Dim fileList As Object
    Set fileList = CreateObject("System.Collections.SortedList")
    Dim fName As String
    Dim fDate As Date
    Dim fPath As String
    Dim i As Integer
    
    fPath = "P:\Everyone\For Baha\Gaming Common\Sands_VirtualCP" & "\BackUpDataFiles\"
    fName = Dir(fPath & "\*.mdb")

    While fName <> ""
        Debug.Print "fName="; Left(fName, Len(fName) - 4)
        fDate = (DateValue(Left(fName, Len(fName) - 4)))
        Debug.Print "fDate="; fDate
        fileList.Item(fDate) = fName
         'get next filename
        fName = Dir()
    Wend
     'see if any files were found
    If fileList.Count = 0 Then
        MsgBox "No files found"
        Exit Sub
    End If
     'cycle through the list and add to listbox
    For i = 0 To fileList.Count - 1
        Me.FilesListBox.AddItem fileList.GetByIndex(i)
    Next
End Sub

The Debug.print statements will display the date before and after conversion using DateValue. You need to make sure that fName is a valid date according to your Excel date and regional settings. Basically, does it look like a date to you?

You might also benefit from looking at what DateValue does: https://support.office.microsoft.co...235-9f4e-f1882dfd6dcd&ui=en-US&rs=en-GB&ad=GB

 

[baha17]

Hi RickXL,

After I brought out the code from userform initialize event, I realize the problem. After the date I still name the files by 8 different categories. Never mind that's an easy task now because your suggestion is working. All I need to alter the code in different select case variations.
Thank you very much for the help.

Cheers
Baha

 

[RickXL]

No problem and thanks for the feedback.

I am glad you fixed it.

Regards,