Author Topic: Help With Sorting Files in VBA Dialog List Box  (Read 2162 times)

0 Members and 1 Guest are viewing this topic.

Vince

  • Newt
  • Posts: 55
Help With Sorting Files in VBA Dialog List Box
« on: October 23, 2014, 11:54:31 AM »
Hello AUGI Members,

I have a VBA routine that allows me to select a directory of drawing files and display them in a dialog list box so I can either choose all of them or individual files for a batch operation.  The difficulty I am having is that when the file list displays in the list box area they are not in alpha numeric sequence.  Can anyone take a look at the sample code below and let me know what needs to be done to sort the list of drawing files so they appear in the list box in the correct sequence....??

Code: [Select]
Private Sub cmdBrowse_Click()
  CDia.DialogTitle = "...Please Select Drawing  .DWG  Directory"
    CDia.Filter = "(*.dwg) |*.dwg|"
    CDia.CancelError = False
    CDia.ShowOpen 'The CommonDialog accounplishes these
    DwgDir = GetPath(CDia.FileName) 'Gets the string path value from the GetPath function
    txtDwgDir.Text = DwgDir 'varible defined as public in module (can pass value to other functions, This will hold the dir path value for each drawing
     Call PopAvail
    If lstAvail.ListCount <> 0 Then
        SBar.SimpleText = "...Select .dwg(s) To Plot"
    Else: SBar.SimpleText = "...Please Select Directory"
    End If
    SBar.Refresh
End Sub

Public Function GetPath(FullStr As String)
    Dim pos As Integer
    Dim LstPos As Integer
    pos = 1 'set the pointer to the first position
    Do Until pos = 0
        pos = InStr(pos, FullStr, "\") 'Reading the string Path
        If pos = 0 Then Exit Do
        pos = pos + 1: LstPos = pos - 1
    Loop 'This will start at the 1 pos and read to till there is no more pos.
    FullStr = Left(FullStr, LstPos) 'Reads the string is found
    GetPath = FullStr 'sets the value
End Function

Public Function PopAvail() 'Gets the object list from the path location (GetPath)
    Dim tempstr As Variant 'Value to count by
    Dim strDwgName As Variant
    lstSelect.Clear
    lstAvail.Clear
    strDwgName = Dir(DwgDir & "*.dwg"): 'set the dir path and Filename value
    tempstr = Dir(DwgDir & "*.dwg"): 'set the dir path and Filename value
    If tempstr <> "" Then
        tempstr = Left(tempstr, Len(tempstr) - 4) '?Start, get lenth of str and counts back 4?, not to path but filename?
        lstAvail.AddItem tempstr
    End If
    Do Until tempstr = ""
        tempstr = Dir
        If tempstr <> "" Then
            tempstr = Left(tempstr, Len(tempstr) - 4)
            lstAvail.AddItem tempstr
        End If
    Loop
End Function




Any assistance would be appreciated.


Best Regards,
Vince
« Last Edit: October 23, 2014, 12:02:45 PM by Vince »

owenwengerd

  • Bull Frog
  • Posts: 439
Re: Help With Sorting Files in VBA Dialog List Box
« Reply #1 on: October 23, 2014, 02:16:20 PM »
It looks like you're using the Windows common file dialog. If so, sorting is controlled by the user, not by code. If you're having trouble controlling sorting on your computer, the problem is not with your code, but with your environment.

Vince

  • Newt
  • Posts: 55
Re: Help With Sorting Files in VBA Dialog List Box
« Reply #2 on: October 23, 2014, 02:43:45 PM »
Thank you for your response Owen....!

That is correct however, the company recently switched to a "cloud base" system and since then when the drawing files are displayed in the list window they are in random order.  Is there a way to resolve this....??   Maybe use another method of selecting the drawing files other than the Windows Common File Dialog....??

Any suggestions / examples would be appreciated....!


Regards,
Vince

owenwengerd

  • Bull Frog
  • Posts: 439
Re: Help With Sorting Files in VBA Dialog List Box
« Reply #3 on: October 23, 2014, 04:39:30 PM »
Of course you could create your own file selection dialog, but I think it would be more useful to correct your system settings. This is not the place to ask about Windows file sorting settings, but I'd start by changing the default sort setting in Explorer to see if that changes anything.

n.yuan

  • Bull Frog
  • Posts: 278
Re: Help With Sorting Files in VBA Dialog List Box
« Reply #4 on: October 24, 2014, 02:14:47 PM »
Well, sorting is one of basic programming skill a programmer should try at least once.

In your case, you need to place the selected file name into a string array, and then sort it before use it to fill up the list box (yes, the list box provided in VBA does not have a way to sort its items).

Simply do an online search for a sorting algorithm, you would get tons of them, some are complicated, some are simple. Even they are not written in VBA code, it would be still easy to translate into VBA code. You might be luck enough to find sorting code in VB/VBA.

For example, I googled "sorting string array in vba", got many links, and this one looks simple and easy:

http://whatapalaver.co.uk/2010/01/vba-snippets-to-sort-arrays-in-vba/