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)
arr = Intersect(.UsedRange, .[a:a]).Value
For Counter = 1 To UBound(arr, 1)
ts.WriteLine arr(Counter, 1)
Set ts = Nothing
Set fso = Nothing
MsgBox "The file has been updated successfully."
MsgBox "Text file not updated at this time."