Problem sorting file dates in an array

T

Trefor

I have read through the various posting trying to get this sorted and found a
bubble sort from Tom Ogilvy, but its not working. Can somene work out what I
may have done wrong? The full code is below:

Private FileNameList(1 To 20, 1 To 2), FileListCount As Integer


Sub Test_File_Date_Sort()
Call GetFileList("C:\temp", FileNameList, FileListCount)
For x = 1 To FileListCount
Cells(x + 2 + FileListCount, "A").Value = FileNameList(x, 2)
Cells(x + 2 + FileListCount, "B").Value = FileNameList(x, 1)
Next
End Sub

Sub GetFileList(GF_Folder As String, FileNameList, FileListCount)
' Complile a list of files in a folder that are sorted oldest to newest

Dim fso As Object
Dim folder As Object
Dim File As Variant

Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder(GF_Folder)
FileListCount = 0

' Load the array with the file name and the date for each file found in
the specific directory
If folder.Files.Count > 0 Then
For Each File In folder.Files
FileListCount = FileListCount + 1
FileNameList(FileListCount, 1) = File
FileNameList(FileListCount, 2) = FileDateTime(File)

Cells(FileListCount, "A").Value = FileNameList(FileListCount, 2)
Cells(FileListCount, "B").Value = FileNameList(FileListCount, 1)

Next File
End If

SortArray (FileNameList)
End Sub

Function SortArray(myArray)
' Tom Ogilvy bubble sort (modified)
' This sort simply takes a 2 dimentional and sorts the value install in
the second element
Dim temp, i, j, k

For i = LBound(myArray, 1) To UBound(myArray, 1) - 1
For j = i + 1 To UBound(myArray, 1)
If myArray(i, 2) > myArray(j, 2) Then
For k = LBound(myArray, 2) To UBound(myArray, 2)
temp = myArray(i, k)
myArray(i, k) = myArray(j, k)
myArray(j, k) = temp
Next
End If
Next
Next

SortArray = myArray
End Function

This is the ouput which suggests that no sort happened at all???

2008 05 02 13:48 file1
2008 05 06 15:08 file2
2008 05 06 15:09 file3
2008 05 06 15:25 file4
2008 05 02 13:45 file5
2008 04 30 08:27 file6
2008 04 30 09:10 file7
2008 05 01 13:00 file8
2008 05 02 13:44 file9
2008 05 02 13:38 file10
2008 05 02 13:40 file11
2008 04 30 08:13 file12


2008 05 02 13:48 file1
2008 05 06 15:08 file2
2008 05 06 15:09 file3
2008 05 06 15:25 file4
2008 05 02 13:45 file5
2008 04 30 08:27 file6
2008 04 30 09:10 file7
2008 05 01 13:00 file8
2008 05 02 13:44 file9
2008 05 02 13:38 file10
2008 05 02 13:40 file11
2008 04 30 08:13 file12

Any ideas?
 
B

Bob Phillips

I created a testbed for that sort function and I get

file12 2008 04 30 08:13
file6 2008 04 30 08:27
file7 2008 04 30 09:10
file8 2008 05 01 13:00
file10 2008 05 02 13:38
file11 2008 05 02 13:40
file9 2008 05 02 13:44
file5 2008 05 02 13:45
file1 2008 05 02 13:48
file2 2008 05 06 15:08
file3 2008 05 06 15:09
file4 2008 05 06 15:25


which suggests that it is sorting, based on the date.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
T

Trefor

Bob,

Thanks for the reply, but I have been pulling out what little hair I have
left :(

OK, just to simplify this I removed all code re DIR and copy the code into a
new sheet (code below), then I formated columns A and B to text. Then I took
the Date/times from another list I made and put a ' before the start of each
line so Excel did not convert it. Then I ran the code and it is still not
sorting, I must be missing something here.

Private FileNameList(1 To 20, 1 To 2), FileListCount As Integer


Sub Test_File_Date_Sort()
For x = 1 To 17
FileNameList(x, 1) = Cells(x, "A").Value
FileNameList(x, 2) = Cells(x, "B").Value
Next

SortArray (FileNameList)

For x = 1 To 17
Cells(x + 18, "A").Value = FileNameList(x, 1)
Cells(x + 18, "B").Value = FileNameList(x, 2)
Next

End Sub

Function SortArray(myArray)
' Tom Ogilvy bubble sort (modified)
' This sort simply takes a 2 dimentional and sorts the value install in
the second element
Dim temp, i, j, k

For i = LBound(myArray, 1) To UBound(myArray, 1) - 1
For j = i + 1 To UBound(myArray, 1)
If myArray(i, 2) > myArray(j, 2) Then
For k = LBound(myArray, 2) To UBound(myArray, 2)
temp = myArray(i, k)
myArray(i, k) = myArray(j, k)
myArray(j, k) = temp
Next
End If
Next
Next

SortArray = myArray
End Function

1 11/05/2008 12:00:24
2 11/05/2008 12:00:24
3 11/05/2008 12:00:35
4 12/05/2008 02:04:28
5 11/05/2008 12:00:43
6 11/05/2008 12:00:51
7 11/05/2008 12:01:00
8 14/05/2008 17:04:41
9 14/05/2008 17:15:37
10 11/05/2008 12:01:23
17 14/05/2008 11:06:18
11 11/05/2008 12:05:01
12 11/05/2008 12:01:31
13 11/05/2008 12:01:39
14 11/05/2008 12:01:47
15 11/05/2008 12:01:54
16 13/05/2008 15:14:10

1 11/05/2008 12:00:24
2 11/05/2008 12:00:24
3 11/05/2008 12:00:35
4 12/05/2008 02:04:28
5 11/05/2008 12:00:43
6 11/05/2008 12:00:51
7 11/05/2008 12:01:00
8 14/05/2008 17:04:41
9 14/05/2008 17:15:37
10 11/05/2008 12:01:23
17 14/05/2008 11:06:18
11 11/05/2008 12:05:01
12 11/05/2008 12:01:31
13 11/05/2008 12:01:39
14 11/05/2008 12:01:47
15 11/05/2008 12:01:54
16 13/05/2008 15:14:10

It does not seem to have changed anything.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top