I have the following code to fill a listbox with file names.
I can not get it to sort it, I found a script but it errors on .list
Fill the listbox
Code:
Me.Listbox1.RowSource = ""
Dim MyFolder As String
Dim MyFile As String
'Dim j As Integer
MyFolder = "C:\dbase"
MyFile = Dir(MyFolder & "\*.accdb")
Do While MyFile <> ""
Listbox1.AddItem MyFile
MyFile = Dir
Loop
Sort - not working
Code:
Dim i As Long
Dim j As Long
Dim temp As Variant
With Me.Listbox1
For j = 0 To Listbox1.ListCount - 2
For i = 0 To Listbox1.ListCount - 2
If .List(i) > .List(i + 1) Then
temp = .List(i)
.List(i) = .List(i + 1)
.List(i + 1) = temp
End If
Next i
Next j
End With
Dim i As Long
Dim j As Long
Dim temp As Variant
With Me.Listbox1
For j = 0 To Listbox1.ListCount - 2
For i = 0 To Listbox1.ListCount - 2
[SIZE=5] If[B][COLOR=Red] .List[/COLOR][/B](i) > .List(i + 1) Then[/SIZE]
temp = .List(i)
.List(i) = .List(i + 1)
.List(i + 1) = temp
End If
Next i
Next j
End With
The equivalent are ItemData and Column, however, the code that follows won't work:
Code:
.List(i) = .List(i + 1)
.List(i + 1) = temp
If your aim is to sort the items in a listbox and the listbox is bound to a table/query then you can sort the table/query instead and refresh the listbox.
If on the other hand it's a Value List, i.e. unbound and the list items added through code, then you want to sort the items before adding to the list.
As per my code in the original post, they are added through a code into a value list, can you please help with the code to sort the items as I thought that when looping through a folder it would put them in the correct sort order but it doesn't.
Code:
Me.Listbox1.RowSource = ""
Dim MyFolder As String
Dim MyFile As String
'Dim j As Integer
MyFolder = "C:\dbase"
MyFile = Dir(MyFolder & "\*.accdb")
Do While MyFile <> ""
Listbox1.AddItem MyFile
MyFile = Dir
Loop
I looked and revised some old code that gets file names from a folder using FileSystemObject. It brings the file names back sorted by name.
You could use the technique to populate a list box - this sample just brings back the file names and prints them to immediate window.
Code:
'---------------------------------------------------------------------------------------
' Procedure : jShowFileInFolderList
' Author : mellon
' Date : 21/05/2015
' Purpose : This procedure gets a list of files from a defined folder using
' fileSystemObject. The files are returned in sorted sequence.
'
'---------------------------------------------------------------------------------------
'
Sub jShowFilesInFolderList()
Dim fs As Variant _
, f As Variant _
, f1 As Variant _
, fc As Variant _
, s As String _
, folderspec As String
10 On Error GoTo jShowFilesInFolderList_Error
20 folderspec = "C:\users\mellon\downloads" 'folder containing the files to list
30 Set fs = CreateObject("Scripting.FileSystemObject")
40 Set f = fs.GetFolder(folderspec)
50 Set fc = f.Files
60 For Each f1 In fc
70 If f1.name Like "a*.*" Then ' to limit to accdb files use "*.accdb"
80 s = s & f1.name
90 s = s & vbCrLf
100 End If
110 Next
120 Debug.Print s
121 Debug.Print vbCrLf & vbCrLf & " Total files " & f.Files.Count ' can comment this line Files in Folder
130 On Error GoTo 0
140 Exit Sub
jShowFilesInFolderList_Error:
150 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure jShowFolderList of Module AWF_Related"
End Sub
My result for "a*.*"
Code:
Access Sample Invoices.zip
access_basics_crystal_080218.pdf
acronisPWD.txt
ActionUA.txt
adt-bundle-windows-x86_64-20140702.zip
AgeGroupQuery_A2003.zip
Analyzer_141028_vbWatchdog_OpenBypass.zip
AS SSD Benchmark.zip
AS SSD Benchmark_inst.exe
ATIH2014_userguide_en-US.pdf
atih_installer_hd_s_e.exe
AutoNum2k.zip
Total files 136
vbaINet's solution is probably the way to go. Just for fun, though, I wonder if the listbox can be sorted this way:
Code:
Dim OutOfSequence as Boolean
Dim i As Long, j as Long
Dim temp As Variant
With Me.Listbox1
Do
OutOfSequence = False
For i = j to .ListCount - 2
If .ItemData(i) > .ItemData(i + 1) Then
temp = .ItemData(i)
j=i
.RemoveItem(i)
.AddItem temp
OutOfSequence = True
Exit For
End If
Next i
Loop While OutofSequence
End With
I remember needing to do this sort of thing and I ended up building a recordset from scratch, populating it, applying a sort to the appropriate field(s) and reading off the values. Even sorting an array (like gemma-the-husky hinted) is another possibility too but it's best to use one of the sorting algorithms, i.e. Bubble Sort or Quick Sort; and I think I used Quick Sort for sorting a dictionary once. There should be code online for both methods otherwise if the poster really needs it I can try and find the project I used them on.
Nevertheless, here's another proof of concept I was just playing with, using command prompt. It can be expanded to sort on other types too so do to it as you wish:
Code:
Public Sub SortFileList(DirPath As String)
Dim objShell As Object
Dim objFso As Object
Dim objStream As Object
Dim strTempFile As String
Const TEMP_FILE As String = "%TEMP%\sortfile100.txt"
Set objShell = CreateObject("WScript.Shell")
strTempFile = objShell.ExpandEnvironmentStrings(TEMP_FILE)
' Sorts the output of dir and outputs it to sortfile100.txt in the TEMP folder
' The /O switch is what does the sorting
objShell.Run "%comspec% /C dir " & _
Chr(34) & DirPath & Chr(34) & _
" /A:-D /B /D /O:N " & _
"> " & Chr(34) & strTempFile & Chr(34), 0, True
' Open the temp file
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objStream = objFso.OpenTextFile(strTempFile, ForReading)
' Return each line as a string and print to the immediate window
With objStream
Do Until .AtEndOfStream
Debug.Print .ReadLine
Loop
End With
objStream.Close
Set objStream = Nothing
Set objFso = Nothing
Set objShell = Nothing
' Delete the temp file
If Dir(strTempFile) <> vbNullString Then Kill strTempFile
End Sub
Fyi: If you don't mind seeing the black command prompt window flash for a second, then the code above can be amended to use the Exec method of WScript instead and from there you can read the output from StdOut, so there will be no need to output to a file. There's just no way of hiding the command prompt window completely using Exec hence the above approach of using Run.
I remember needing to do this sort of thing and I ended up building a recordset from scratch, populating it, applying a sort to the appropriate field(s) and reading off the values.
