View Full Version : [SOLVED:] Sort contents in a text file.
entwined
05-23-2013, 10:20 PM
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
05-23-2013, 11:13 PM
open file, select the first cell, then Data, Sort
entwined
05-24-2013, 05:02 PM
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
05-24-2013, 05:38 PM
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
05-24-2013, 10:40 PM
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
05-25-2013, 06:42 AM
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
05-25-2013, 09:36 AM
or a bat file with the command
Sort x:\sort\sort.txt /O x:\sort\sort_out.txt
entwined
05-26-2013, 04:01 PM
Gary nailed it! Thanks a lot guys for your inputs. Apologies for posting in the wrong section....
Rusticles
09-21-2015, 12:36 AM
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
09-21-2015, 05:09 AM
'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
09-21-2015, 07:44 AM
Thanks!
Rusticles
09-21-2015, 05:00 PM
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
09-21-2015, 05:08 PM
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
09-21-2015, 06:50 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2025 vBulletin Solutions Inc. All rights reserved.