Sort contents in a text file.

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....

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

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?

How about something like:

Code:

---

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

---

Quote:

---

Originally Posted by 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?

---

You posted on excel forum, not vbs forum.

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

Code:

---

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

---

or a bat file with the command

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

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

Quote:

---

Originally Posted by GarysStudent

How about something like:

Code:

---

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

Code:

---

'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

---

Thanks!

Code:

---

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:

Code:

---

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

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

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.

Code:

---

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

---