Solved

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

Posted on 2009-05-19
3
169 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
TT Column Arrange 10 28
How to make an ADE file by code? 11 38
Three Dimensional Journey Map in Excel 8 28
Gantt chart 2 12
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 article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

705 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

18 Experts available now in Live!

Get 1:1 Help Now