Wednesday, January 31, 2024

[SOLVED] VBA - Sorting text file by number sequence of 20 characters each

Issue

I am trying to sort a text file from VBA, using Wscript.Shell.

However, I am unable to sort by the first 20 characters of each line. It's as if the command only considered the first 10 characters to sort.

Below, the result (pasted only one little part of the text file)

Sorted text:

59342768471700000028
59342768471300171816
59342768472400038039
59342768472400055526
59342768470900142917
59342768470300108625
59342768472200028114
59342768472200033063
59342768471900014592
59342768471000152827
59342768472500038040
59342768472500055527
59342768471200167530
59342768470100084909
59342768471100157308
59342768471100162422
59342768471700191485

Does anyone know if it is normal or the solution?

Here is the code:

    Sub ClassificarSeq()

    Dim myoutput as string, myoutput2 as string, pastanova as string, contador as string, batfile as string, nomedobat as string, FSO as object
    Myoutput = "C:\fileunsorted.txt"

    Set FSO = Nothing
    Set FSO = CreateObject("Scripting.FileSystemObject")
    batfile = "C:\TEMP\ClassificarTXT2.txt"
    pastanova = Left(myOutput, InStrRev(myOutput, "\"))
    nomedobat = "C:\TEMP\ClassificarTXT2" & horario & ".bat"
    myoutput2 = Left(myOutput, Len(myOutput) - 4) & "_Classificado2.txt"

    Open batfile For Output As #3

    Print #3, "cd " & """" & pastanova & """"
    Print #3, "sort " & """" & myOutput & """" & " /O " & """" & myoutput2 & """"
    Print #3, "del /f " & """" & myOutput & """"

    Close #3

    contador = 1

    Name "C:\TEMP\ClassificarTXT2.txt" As nomedobat

    Dim wsh As Object
    Set wsh = VBA.CreateObject("WScript.Shell")
    Dim waitOnReturn As Boolean: waitOnReturn = True
    Dim windowStyle As Integer: windowStyle = 1

    wsh.Run nomedobat, windowStyle, waitOnReturn

    batfile = nomedobat

    Close #2

    Name myoutput2 As myOutput

    Close #3

    Kill batfile

    End Sub

Solution

As requested in the OP, here is a simple solution:

Public Sub fnSortByArrList()
    Dim arrList As Object
    Dim FSO As Object
    Dim objFile As Object
    Dim varKey As Variant
    Dim strFileToSort As String
    
    ' Create a new ArrayList object
    Set arrList = CreateObject("System.Collections.ArrayList")
    ' load the file with data to be sorted
    Set FSO = CreateObject("Scripting.FileSystemObject")
    strFileToSort = "c:\temp\classificarTXT.txt"
    'open the file and read data
    Set objFile = FSO.OpenTextFile(strFileToSort, ForReading, False, TristateUseDefault)
    'Put values in arrList
    Do While objFile.AtEndOfStream <> True
        arrList.Add Trim(objFile.ReadLine)
    Loop
    ' Sort the keys on arrList
    arrList.Sort
    ' Read through the sorted keys and write them to the new file
    Open "C:\temp\SortedData.txt" For Output As #1
    For Each varKey In arrList
        Print #1, varKey
    Next varKey
    Close #1
    
    ' Clean up
    Set arrList = Nothing
    Set objFile = Nothing
    Set FSO = Nothing
End Sub

You can improve it, by removing the hard-coded values and passing them within variables.



Answered By - ALeXceL
Answer Checked By - Terry (WPSolving Volunteer)
at