PDA

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