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

 

[Eric Penfold]

FolderName is working \\dc01\Company\R&D\Drawing Nos\10001-10050\10016 is correct

Still the FilesArray is saying nothing

 

[John_w]

Is .NET Framework 3.5 enabled? See my earlier post.

FolderName should end with a backslash.

 

[Eric Penfold]

Is .NET Framework 3.5 enabled? See my earlier post.

I am waiting for my IT Boss to allow access when he does I will let you know if it works.

 

[Eric Penfold]

I`ve enabled the NET Framework 3.5 but now Files Array says run time error 5

 

[Eric Penfold]

Sorry ignore the previous message how can I add items to listbox with an array. I have tried below but on the

.List FileName

it says invalid use of property.

Sub ListFiles()

        Dim FilesArray As Object
        Dim SubPath    As String
        Dim FolderName As String
        Dim FileName   As String
        Dim s          As String
        Dim i          As Long
        Dim CmbData
        
        PdfDrawingList.Clear

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

         SourcePath = "\\dc01\Company\R&D\Drawing Nos"

        If Not (Me.OpenDrawing = "") Then

            If Val(Me.OpenDrawing) >= 10001 And Val(Me.OpenDrawing) <= 10050 Then
                SubPath = "10001-10050"
            ElseIf Val(Me.OpenDrawing) >= 10051 And Val(Me.OpenDrawing) <= 10100 Then
                SubPath = "10051-10100"
            ElseIf Val(Me.OpenDrawing) >= 10101 And Val(Me.OpenDrawing) <= 10150 Then
                SubPath = "10101-10150"
            ElseIf Val(Me.OpenDrawing) >= 10151 And Val(Me.OpenDrawing) <= 10200 Then
                SubPath = "10151-10200"
            End If
    
        FolderName = (SourcePath & "\" & SubPath & "\" & Int(CmbData(0))) & "\"
       
        Set FilesArray = CreateObject("System.Collections.ArrayList")
    
        FileName = Dir(FolderName & "*.pdf", vbReadOnly)
        Do While FileName <> vbNullString
            FilesArray.Add FileName
            FileName = Dir()
        Loop

        FilesArray.Sort

            s = ""
            For i = 0 To FilesArray.Count - 1
                s = s & FilesArray(i) & vbCrLf
            Next
            
            FileName = s
            
            With Me.PdfDrawingList
            .List FileName
            End With

        End If
        
    End Sub

 

[John_w]

how can I add items to listbox with an array

With the ArrayList object? See the For ... Next loop in my first reply.

 

[Eric Penfold]

OK Thanks that`s working fine now.
In the List it 10016-1 then 10016-10 but there is a 10016-6 which i would like to be in order. Is it possible to do this?

 

[John_w]

In the List it 10016-1 then 10016-10 but there is a 10016-6 which i would like to be in order. Is it possible to do this?

Yes, to sort in numerical order the sort routine must compare two file name strings using the StrCmpLogicalW function, instead of the normal > or < string comparison operators which sort in ASCII order.

Whilst it is possible for the ArrayList class to sort strings in numerical order, it involves the following steps:

• Add a reference to mscorlib.dll (early binding of ArrayList must be used).
• Add a 'comparer' class module which compares two file names using StrCmpLogicalW.
• Call ArrayList.Sort_2 with the comparer class, instead of ArrayList.Sort.

It will be easier for you to use a normal VBA array and sort routine which calls StrCmpLogicalW, as I describe here:

Add a new standard module and paste in the following code:

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function StrCmpLogicalW Lib "shlwapi.dll" (ByVal string1 As String, ByVal string2 As String) As Long
#Else
    Private Declare Function StrCmpLogicalW Lib "shlwapi.dll" (ByVal string1 As String, ByVal string2 As String) As Long
#End If

Public Sub Insertion_Sort(ByRef arr As Variant)

    Dim i As Long, j As Long
    Dim temp As String

    For i = 1 To UBound(arr)
        temp = arr(i)
        For j = i To 1 Step -1
            If StrCmpLogicalW(StrConv(arr(j - 1), vbUnicode), StrConv(temp, vbUnicode)) = 1 Then
                arr(j) = arr(j - 1)
            Else
                Exit For
            End If
        Next
        arr(j) = temp
    Next

End Sub

Referring to your OP, replace the whole ListFiles routine with:

Sub ListFiles()

    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

        Dim fileName As String
        Dim filesArray() As String
        Dim i As Long

        FolderName = SourcePath & "\" & subPath & "\" & Int(CmbData(0)) & "\"
        i = 0
        fileName = Dir(FolderName & "*.pdf", vbReadOnly)
        Do While fileName <> vbNullString
            ReDim Preserve filesArray(i)
            filesArray(i) = fileName
            i = i + 1
            fileName = Dir()
        Loop

        If i > 0 Then
            Insertion_Sort filesArray
            For i = 0 To UBound(filesArray)
                Me.PDFdrawingList.AddItem filesArray(i)
            Next
        End If

    End If
    
End Sub

 

[Eric Penfold]

Thanks very much for this

This part of the code is red sorry no idea why

 Private Declare Function StrCmpLogicalW Lib "shlwapi.dll" (ByVal string1 As String, ByVal string2 As String) As Long

Again Thanks!

 

[mikerickson]

From the OP code, replace

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

with

With Me.PdfDrawingList
    Do While FSOFile <> ""
        For i = 0 to .ListCount - 1
            If FSOFile < .List(i) Then
                .AddItem FSOFile, i
                Goto NextLoop
            End If
        Next i
        .AddItem FSOFile
NextLoop:
         FSOFile = Dir
     Loop
End With