Sort contents in a text file.

[entwined]

Hello guys,

I need help in sorting the contents of a text file. It only contains a simple list:

redws002
atlws001
redws001
atlws003
sfmws001
atlws002
sfmws002

I want a macro that will sort those list so the contents of the text file should now be like this:

atlws001
atlws002
atlws003
redws001
redws002
sfmws001
sfmws002

Any help will be very much appreciated. Thanks in advance....

[patel]

open file, select the first cell, then Data, Sort

[entwined]

It is not an excel file but just a simple text file. I know it can be opened in excel but is there any way to sort it without using excel? Like a vbscript?

[GarysStudent]

How about something like:

Sub cutaneous()
Dim s As String

Close #1
Close #2
Open "C:\TestFolder\sample.txt" For Input As #1
Open "C:\TestFolder\samp2.txt" For Output As #2

With CreateObject("System.Collections.ArrayList")
    Do Until EOF(1)
        Line Input #1, s
        .Add Trim$(CStr(s))
    Loop
    .Sort
    ary = .ToArray
    For L = LBound(ary) To UBound(ary)
        Print #2, ary(L)
    Next
End With

Close #1
Close #2
End Sub

[patel]

It is not an excel file but just a simple text file. I know it can be opened in excel but is there any way to sort it without using excel? Like a vbscript?

You posted on excel forum, not vbs forum.

[Kenneth Hobs]

GarysStudent's method works fine if you have vb.net framework support files installed.

Shell "Sort x:\sort\sort.txt /O x:\sort\sort_out.txt", vbHide

[patel]

or a bat file with the command

Sort x:\sort\sort.txt /O x:\sort\sort_out.txt

[entwined]

Gary nailed it! Thanks a lot guys for your inputs. Apologies for posting in the wrong section....

[Rusticles]

How about something like:

Sub cutaneous()
Dim s As String

Close #1
Close #2
Open "C:\TestFolder\sample.txt" For Input As #1
Open "C:\TestFolder\samp2.txt" For Output As #2

With CreateObject("System.Collections.ArrayList")
Do Until EOF(1)
Line Input #1, s
.Add Trim$(CStr(s))
Loop
.Sort
ary = .ToArray
For L = LBound(ary) To UBound(ary)
Print #2, ary(L)
Next
End With

Close #1
Close #2
End Sub

Hi Gary,

Can you advise about how to achieve a descending sort order with this method?

Russell

[Kenneth Hobs]

'http://www.vbaexpress.com/forum/showthread.php?48491Function ArrayListSort(sn As Variant, Optional bAscending As Boolean = True)
    With CreateObject("System.Collections.ArrayList")
        Dim cl As Variant
        For Each cl In sn
            .Add cl
        Next
         
        .Sort 'Sort ascendending
        If bAscending = False Then .Reverse 'Sort and then Reverse to sort descending
        ArrayListSort = .ToArray()
    End With
End Function

[GarysStudent]

Thanks!

[Rusticles]

Optional bAscending As Boolean = True)
    With CreateObject("System.Collections.ArrayList")
        Dim cl As Variant
        For Each cl In sn
            .Add cl
        Next
         
        .Sort 'Sort ascendending
        If bAscending = False Then .Reverse 'Sort and then Reverse to sort descending
        ArrayListSort = .ToArray()
    End With
End Function

Thanks Kenneth. I pasted a section of your code into Gary's Code as per emboldened line below:

Sub cutaneous2()
Dim s As String
Close #1
Close #2
Open "C:\CMSTemp\appendCDTData_CHANGED.txt" For Input As #1
Open "C:\CMSTemp\appendCDTData_CHANGED2.txt" For Output As #2
With CreateObject("System.Collections.ArrayList")
Do Until EOF(1)
Line Input #1, s
.Add Trim$(CStr(s))
Loop
.Sort
If bAscending = False Then .Reverse 'Sort and then Reverse to sort descending 'Kenneth Hobbs code
ArrayListSort = .ToArray() 'Kenneth Hobbs code
ary = .ToArray
For L = LBound(ary) To UBound(ary)
Print #2, ary(L)
Next
End With
Close #1
Close #2
End Sub

'=================================
I prefer to use Option Explicit but I need to work on fully declaring all the variables required for this code to work. A previous threader (you in fact) commented that for Gary's code to ececute it has a dependency on an installation of vb.net framework support files installed being installed. Do you have any advice on the scale of work required to achieve a fully portable executable Sub? Thanks Again - Russ

[Rusticles]

You Function Kenneth is fully declared and satisfied the explicit nature of option explict - I'm now looking at how to call your function to perform my sorting

[Kenneth Hobs]

It is best to start your own threads. You can always post a link to a thread that might be relevant.

When pasting code, please paste between code tags. Click the # icon to insert tags.

These days, most Microsoft Windows computers will have the framework files installed.

Sub ken()  
  Dim a() As Variant
  
  a() = Array(5, 6, 1, 11)
  MsgBox Join(a, vbLf)
  
  a() = ArrayListSort(a)
  MsgBox Join(a, vbLf)
  
  a() = ArrayListSort(a, False)
  MsgBox Join(a, vbLf)
End Sub

Function ArrayListSort(sn As Variant, Optional bAscending As Boolean = True)
    With CreateObject("System.Collections.ArrayList")
        Dim cl As Variant
        For Each cl In sn
            .Add cl
        Next
         
        .Sort 'Sort ascendending
        If bAscending = False Then .Reverse 'Sort and then Reverse to sort descending
        ArrayListSort = .toarray()
    End With
End Function