How to put values in order in a listbox ascending

[Eric Penfold]

This code takes values from a folder then puts them into a listbox . But how can sort numbers numerically

Sub ListFiles()

        Dim FSOLibary As FileSystemObject
        Dim FSOFolder As Object
        Dim FSOFile As String
        Dim FolderName As String
        Dim SourcePath As String
        Dim SubPath As String
        Dim CmbData

        If Not Me.OpenDrawing = "" Then

        CmbData = Split(Me.OpenDrawing.Value, "-")
        CmbData(0) = Replace(CmbData(0), "-", "")

        SourcePath = "\\dc01\Company\R&D\Drawing Nos"
        
    If Val(CmbData(0)) >= 10001 And Val(CmbData(0)) <= 10050 Then
        SubPath = "10001-10050"
    ElseIf Val(CmbData(0)) >= 10051 And Val(CmbData(0)) <= 10100 Then
        SubPath = "10051-10100"
    ElseIf Val(CmbData(0)) >= 10101 And Val(CmbData(0)) <= 10150 Then
        SubPath = "10101-10150"
    ElseIf Val(CmbData(0)) >= 10151 And Val(CmbData(0)) <= 10200 Then
        SubPath = "10151-10200"
    End If


        Me.PdfDrawingList.Clear

        On Error Resume Next

        FolderName = (SourcePath & "\" & SubPath & "\" & Int(CmbData(0))) & "\"
        Set FSOLibary = New Scripting.FileSystemObject
        Set FSOFolder = FSOLibary.GetFolder(FolderName)
        FSOFile = Dir(FSOFolder & "\" & "*.pdf", vbReadOnly)

         Do While FSOFile <> ""
         Me.PdfDrawingList.AddItem FSOFile
         FSOFile = Dir
         Loop
         
         End If
        
End Sub

 

[John_w]

Try replacing this part of your code:

        FolderName = (SourcePath & "\" & SubPath & "\" & Int(CmbData(0))) & "\"
        Set FSOLibary = New Scripting.FileSystemObject
        Set FSOFolder = FSOLibary.GetFolder(FolderName)
        FSOFile = Dir(FSOFolder & "\" & "*.pdf", vbReadOnly)

         Do While FSOFile <> ""
         Me.PdfDrawingList.AddItem FSOFile
         FSOFile = Dir
         Loop

with:

    Dim filesArray As Object
    Dim fileName As String
    Dim i As Long
   
    Set filesArray = CreateObject("System.Collections.ArrayList")

    FolderName = (SourcePath & "\" & subPath & "\" & Int(CmbData(0))) & "\"
    fileName = Dir(FolderName & "*.pdf", vbReadOnly)
    Do While fileName <> vbNullString
        filesArray.Add fileName
        fileName = Dir()
    Loop
   
    filesArray.Sort

    For i = 0 To filesArray.Count - 1
        Me.PdfDrawingList.AddItem filesArray(i)
    Next

 

[Eric Penfold]

Thanks for your help

I`ve tried your code but the FilesArray seems to not be working?

Sub ListFiles()

        Dim FilesArray      As Object
        Dim i                     As Long
        Dim FolderName  As String
        Dim SourcePath   As String
        Dim SubPath        As String
        Dim FileName      As String
        Dim CmbData
         
        

        If Not Me.OpenDrawing = "" Then
        
        Me.PdfDrawingList.Clear

        CmbData = Split(Me.OpenDrawing.Value, "-")
        CmbData(0) = Replace(CmbData(0), "-", "")

        SourcePath = "\\dc01\Company\R&D\Drawing Nos"
        
    If Val(CmbData(0)) >= 10001 And Val(CmbData(0)) <= 10050 Then
        SubPath = "10001-10050"
    ElseIf Val(CmbData(0)) >= 10051 And Val(CmbData(0)) <= 10100 Then
        SubPath = "10051-10100"
    ElseIf Val(CmbData(0)) >= 10101 And Val(CmbData(0)) <= 10150 Then
        SubPath = "10101-10150"
    ElseIf Val(CmbData(0)) >= 10151 And Val(CmbData(0)) <= 10200 Then
        SubPath = "10151-10200"
    End If

   On Error Resume Next
        
    Set FilesArray = CreateObject("System.Collections.ArrayList")

    FolderName = (SourcePath & "\" & SubPath & "\" & Int(CmbData(0))) & "\"
    FileName = Dir(FolderName & "*.pdf", vbReadOnly)
    Do While FileName <> vbNullString
        FilesArray.Add FileName
        FileName = Dir()
    Loop
         
         
         For i = 0 To FilesArray.Count - 1
        Me.PdfDrawingList.AddItem FilesArray(i)
    Next
    
    End If
        
End Sub

 

[Eric Penfold]

Please could somebody help?

 

[John_w]

I`ve tried your code but the FilesArray seems to not be working?

In what way isn't it working?

You omitted the filesArray.Sort line.

 

[Eric Penfold]

The

FilesArray

always says Nothing

 

[John_w]

As a test, does this work for you? Change the hard-coded FolderName to suit.

Sub ListFiles2()

    Dim FolderName As String
    Dim filesArray As Object
    Dim fileName As String
    Dim i As Long
    
    FolderName = "C:\PDF\"
   
    Set filesArray = CreateObject("System.Collections.ArrayList")

    fileName = Dir(FolderName & "*.pdf", vbReadOnly)
    Do While fileName <> vbNullString
        filesArray.Add fileName
        fileName = Dir()
    Loop
   
    filesArray.Sort

    Me.PDFdrawingList.Clear
    For i = 0 To filesArray.Count - 1
        Me.PDFdrawingList.AddItem filesArray(i)
    Next
        
End Sub

 

[John_w]

Search for 'Turn Windows features on or off' in Windows and make sure .NET Framework 3.5, which is required for System.Collections.ArrayList, is enabled:

 

[Eric Penfold]

Sorry i can`t hard code it because it is driven by users filling in a text box

 

[John_w]

Just run this macro instead with a suitable FolderName, but note the MsgBox at the end might not display all the files (it has a limit of 1023 chars), but what is displayed should be in ascending order.

Sub ListFiles2()

    Dim FolderName As String
    Dim filesArray As Object
    Dim fileName As String
    Dim i As Long
    
    FolderName = "C:\PDF\"   'CHANGE THIS
   
    Set filesArray = CreateObject("System.Collections.ArrayList")

    fileName = Dir(FolderName & "*.pdf", vbReadOnly)
    Do While fileName <> vbNullString
        filesArray.Add fileName
        fileName = Dir()
    Loop
   
    filesArray.Sort

    Dim s As String
    s = ""
    For i = 0 To filesArray.Count - 1
        s = s & filesArray(i) & vbCrLf
    Next
    MsgBox s
    
End Sub