Solved

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

Posted on 2009-05-19
3
170 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:fselliott
3 Comments
 
LVL 2

Accepted Solution

by:
quickcat88 earned 500 total points
ID: 24426343
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
 

Author Closing Comment

by:fselliott
ID: 31583219
Yes! Thank you, that was exactly what I needed.
0
 
LVL 33

Expert Comment

by:Norie
ID: 24434625
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VB 6 error 5 in windows 10 but not in XP 7 39
vbModal 12 30
Mac Excel column treating text as date 2 27
Most Consistent Performer 4 19
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now