How do I sort Excel data before exporting to text file?

I am using a VB script to export data in a specified column to a text file (it was supplied via question ID: 24420612), the code is below.

When the txt file is written, it leaves empty lines, or carriage returns on the lines that did not have data. Is there a way to remove the empty lines, or sort the column when exporting to the text file so that the empty lines disappear?
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    Dim UpdateFile As Long
    Dim fso As Object
    Dim ts As Object
    Dim arr As Variant
    Dim Counter As Long
    
    Const FilePath As String = "w:\extract.txt"
    
    UpdateFile = MsgBox("Do you want to update the Extract file?", vbQuestion + vbYesNo, "Update")
    
    If UpdateFile = vbYes Then
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set ts = fso.CreateTextFile(FilePath, True)
        With ThisWorkbook.Worksheets("Extract")
            arr = Intersect(.UsedRange, .[a:a]).Value
        End With
        For Counter = 1 To UBound(arr, 1)
            ts.WriteLine arr(Counter, 1)
        Next
        ts.Close
        Set ts = Nothing
        Set fso = Nothing
        MsgBox "The file has been updated successfully."
    Else
        MsgBox "Text file not updated at this time."
    End If
    
End Sub

Open in new window

fselliottAsked:
Who is Participating?
 
quickcat88Connect With a Mentor Commented:
How about check whether the line is empty before writing it to file?

    Dim aline As String
...
        For Counter = 1 To UBound(arr, 1)
            aline = Trim(arr(Counter, 1))
            If (Not Len(aline) = 0) Then
                ts.WriteLine aline
            End If
        Next
0
 
fselliottAuthor Commented:
Yes! Thank you, that was exactly what I needed.
0
 
NorieVBA ExpertCommented:
If you do want to sort.

Dim UpdateFile As Long
Dim fso As Object
Dim ts As Object
Dim rng As Range
Dim arr As Variant
    
    Const FilePath As String = "c:\extract.txt"
    
    UpdateFile = MsgBox("Do you want to update the Extract file?", vbQuestion + vbYesNo, "Update")
    
    If UpdateFile = vbYes Then
    
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set ts = fso.CreateTextFile(FilePath, True)
        With ThisWorkbook.Worksheets("Sheet3")
            Set rng = Intersect(.UsedRange, .Range("A:A"))
            rng.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess
 
            Set rng = Intersect(.UsedRange, .Range("A:A"))
        
            arr = rng.Value
            
        End With
        
        arr = Join(Application.Transpose(arr), vbCrLf)
        
        ts.WriteLine arr
        
        ts.Close
        
        Set ts = Nothing
        Set fso = Nothing
        MsgBox "The file has been updated successfully."
    Else
        MsgBox "Text file not updated at this time."
    End If

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.