Listing Files from a folder - in alpha order

[Maturi]

Hi,

I trying to list the files in a folder and show the output list in a Combo Box (CB_List) using the code

Dim fso As New FileSystemObject
Dim f As File
Dim fld As Folder
Dim FilesinFolder As Files
Dim MyPath As String

MyPath = "c:\docs"

Set fld = fso.GetFolder(MyPath)

Set FilesinFolder = fld.Files

For Each f In FilesinFolder
	Pack = Pack & ";" & f.Name
Next
    Me!CB_List.RowSource = Pack

The files in 'FilesinFolder' appear to be in a random order (when I list them out in the combo box).

Is there a way to order them by name/alpha order either before doing Me!CB_List.RowSource = Pack

or after it's in the combo box??

Thanks

M

 

[bboffin]

Try:
Me!CB_List.Sorted = True

Bob Boffin

 

[Maturi]

Bob

Thanks - but I'm getting the message...

"Object does not support this property or method" for the '.Sorted =True'

Is there a DLL/Reference I need to include? Or is it something else.

Apreciate your help

Mat

 

[ZmrAbdulla]

Maturi,
Are you sure you are using it in VB6? I think it is AccessVBA

________________________________________________________
Zameer Abdulla
Help to find Missing people
Do not cut down the tree that gives you shade.

 

[Maturi]

You are right - It is Access VBA

Wrong Forum.

Can you still help?

 

[ZmrAbdulla]

Why not you are using the modified function bby TheAceman1 in thread702-1158417? It is sorted by alpha

    Dim sPath As String, sFile As String, Pack As String
    sPath = "c:\docs\"
    sFile = Dir(sPath, vbDirectory)
    Me.CB_List.RowSourceType = "Value List"

    Do While sFile <> ""
        If sFile <> "." And sFile <> ".." Then
            If Pack <> "" Then
                Pack = Pack & ";" & sFile
            Else
                Pack = sFile
            End If
        End If

        sFile = Dir
    Loop

    Me!CB_List.RowSource = Pack

________________________________________________________
Zameer Abdulla
Help to find Missing people
Do not cut down the tree that gives you shade.

 

[Maturi]

Zameer,

Tried that approach - yes it does do a similar job but it still does not order the files in name order. It seems to depend on the folder you open.

Any other suggestions?

Other wise I will post the query in the Access VBA forum

 

[ZmrAbdulla]

But it is giving a sorted result for me.

Anyway if you want to post in Access VBA forum then post it. don't remember to link both threads in to it.

________________________________________________________
Zameer Abdulla
Help to find Missing people
Do not cut down the tree that gives you shade.

 

[ZmrAbdulla]

I mean don't forget to link..

________________________________________________________
Zameer Abdulla
Help to find Missing people
Do not cut down the tree that gives you shade.

 

[BobRodes]

You could just use the old DOS sort pipe:

Shell "cmd /c dir | sort /+39 /O temp.txt"

will put a sorted list of the directory in a file called temp.txt. Of course, the folder names will be sorted into the list as well, so you'll have to remove them in your routine that populates your combo box.

HTH

Bob

 

[gmmastros]

Bob,

That's a pretty good idea. Expanding on that, I came up with this...

    Dim FSO As Scripting.FileSystemObject
    Dim cFolder As String
    Dim cFiles As String
    Dim arFiles() As String
    Dim i As Long
    
    cFolder = "C:\"
    
    Shell "cmd /c Dir """ & cFolder & "*.*"" /b /o /a:-d > """ & App.Path & "\Files.txt"""
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    cFiles = FSO.OpenTextFile(App.Path & "\Files.txt", ForReading).ReadAll
    Set FSO = Nothing
    
    arFiles = Split(cFiles, vbCrLf)
    For i = LBound(arFiles) To UBound(arFiles)
        Debug.Print arFiles(i)
    Next

Check out all those command line arguments to the dir command.

/b removes the header stuff
/o sorts the list alphabetically
/a:-d removes directories from the listing

The original poster wanted the list in a combo box, so instead of debug.print you would need to add the items to the combo box.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[BobRodes]

Well, that got me going. I typed "help dir" on the command line. It would seem that dir has grown up since the old DOS 3.x days. George, I played around a bit and I have a couple of observations: *.* isn't necessary (might still be a good idea, of course), since dir does that anyway, and /b also sorts. So, one could get away with

Shell "cmd /c Dir """ & cFolder & """ /b /a-d > """ & App.Path & "\Files.txt"""

if one had a bent for simplification over specification. In any case, the dir switches are a definite improvement over using the sort pipe.

On the other hand, the sort pipe isn't obsolete: given that you can sort by any column you like, you can do things you can't do with dir switches. For example,

shell "cmd /c dir \myfolder | sort /+22"

will sort from smallest file to largest, with all directories sorted to the bottom.

Bob

 

[dilettante]

Here's a WSH script, easily converted to VB:

sortdir.wsf

<job>
  <object id="FSO" progid="Scripting.FileSystemObject"/>
  <object id="SORTER" progid="ADODB.Recordset"/>
  <reference object="ADODB.Recordset"/>
  <script language="VBScript">
    Option Explicit

    Dim f, str

    With SORTER
      .Cursorlocation = adUseClient
      .Fields.Append "FileName", adVarWChar, 255
      .Open
      .Sort = "FileName ASC"

      For Each f In FSO.GetFolder("C:\").Files
        .AddNew
        .Fields("FileName").Value = f.Name
      Next

      str = ""
      .MoveFirst
      Do While Not .EOF
        str = str & .Fields("Filename").Value & vbNewLine
        .MoveNext
      Loop
      .Close
      MsgBox str
    End With
  </script>
</job>

Standalone ADO Recordsets are lighter weight than one might think. I've even used them as the workspace in line-oriented embedded text editors that only had to handle 2000 lines or so.

 

[gmmastros]

dilettante,

That's another good idea. Maybe we can get Bob to do some benchmark testing.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[strongm]

Something like the following should also work:

strComputer = "."
Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colFiles = objWMIService.ExecQuery("Select * from CIM_DataFile where Path = '\\file downloads\\'") ' order by name ascending")

For Each objFile In colFiles
Debug.Print objFile.FileName
Next

 

[BobRodes]

Well. Thanks again to strongm for pointing out another huge pile of stuff I knew nothing about. For some background, I started looking at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/anch_wmi.asp

strongm's code works fine in the test I did.

Bob

 

[strongm]

Bob, I've dropped another WMI solution into thread222-1159645 ...

 

[BobRodes]

Thank you sir. For those interested,

http://msdn.microsoft.com/library/d...mi/wmi_tasks_for_scripts_and_applications.asp

reveals all kinds of toys.