Export a range of data in excel to ascii plain text formatted table csv or txt file using vba

Question

I need to export only a range of data from Excel or LibreOffice Calc file to ascii plain text table - text file. The output should be in the format as follows :

+--------+--------------+--------+
| Name   | Place        | Phone  |
+========+==============+========+
| JOHN   | TEXAS        | 123456 |
+--------+--------------+--------+
| ROBERT | INDIANAPOLIS | 234567 |
+--------+--------------+--------+
| JEANNE | CAMBODIA     | 345678 |
+--------+--------------+--------+
| ANN    | CANADA       | 456789 |
+--------+--------------+--------+
| RICK   | HAGUE        | 567890 |
+--------+--------------+--------+

Is it possible using vba ? And yes, the numbers should be Right aligned.

Or like this :

+--------+--------------+--------+
| Name   | Place        | Phone  |
+========+==============+========+
| JOHN   | TEXAS        | 123456 |
| ROBERT | INDIANAPOLIS | 234567 |
| JEANNE | CAMBODIA     | 345678 |
| ANN    | CANADA       | 456789 |
| RICK   | HAGUE        | 567890 |
+--------+--------------+--------+

It's not clear whether you mean it should be *exactly* like you show, including the borders? What have you tried - anything ? — Tim Williams, Sep 16 '16 at 04:30
@TimWilliams, yes I need exactly as shown above. But if the horizontal lines after each row is not possible, then it is ok, but atleast headers should have line below it. — adam1969in, Sep 16 '16 at 04:38
Yes this can be done... see this answer http://stackoverflow.com/questions/11503174/how-to-create-and-write-to-a-txt-file-using-vba — Rosetta, Sep 16 '16 at 04:53
the output depends on how you write to the txt file. i see u don't want it to be delimited but fixed width.. yes can be done. u want horizontal lines.. yes can be done.. In short, just design ur procedure how u would export the text line-by-line to the txt file until it suits your need. — Rosetta, Sep 16 '16 at 04:58

score 1 · Answer 1 · answered Sep 16 '16 at 03:18

1

If I'm not mistaken, a plain text file won't justify a column at all.

That said, give this a try: http://www.excel-easy.com/vba/examples/write-data-to-text-file.html

answered Sep 16 '16 at 03:18

thedatadavis

79
6

vim does that, why not vba ? – adam1969in Sep 16 '16 at 03:25
@adam1969in i beg to differ, this is exactly what u need. – Rosetta Sep 16 '16 at 05:01
1

@chris_chringle, since OP requirement is fixed width text file, it can always design the procedure for a column to look like a right-aligned column – Rosetta Sep 16 '16 at 05:02
@Rosetta I see now - as a fixed width file it's certainly doable – thedatadavis Sep 16 '16 at 05:04
@Rosetta, the link shows how to write to a CSV file. In that case, there is no column width. I need it to be aligned like a Table, as shown in my question. It should be something like finding the column width of the largest value and then allow a space on either sides of the vertical border, so that alignment is effected. – adam1969in Sep 16 '16 at 07:13

score 1 · Answer 2 · answered Sep 28 '16 at 02:36

Well, I got it working.

Sub Convert2Text()
    Dim i As Integer, k As Integer, n As Integer, m As Integer
    Dim Rng As Range, Dpath As String
    Dim FSO As Scripting.FileSystemObject
    Dim TxtFile As TextStream
    Dim MaxLen As Integer

        Set Rng = Application.InputBox("Please Select range.", "-Convert to Text-", , -Range("R2").Left, Range("R2").Top, , , 8)
        If Rng Is Nothing Then
            Exit Sub
        End If
        Dpath = Environ("USERPROFILE") & "\Documents\"
        Set FSO = New FileSystemObject
        Set TxtFile = FSO.CreateTextFile(Dpath & "Outputfile.txt", True)

            MaxLen = 1
            For i = 1 To Rng.Columns.Count
                For k = 1 To Rng.Rows.Count
                If MaxLen < Len(Rng.Cells(i, k)) Then
                    MaxLen = Len(Rng.Cells(i, k))
                End If
                Next k
            Next i
            MaxLen = MaxLen + 4

            For i = 1 To Rng.Columns.Count
                TxtFile.Write "+"
                    For m = 1 To MaxLen
                        TxtFile.Write "="
                    Next m
            Next i
                TxtFile.Write "+"
                TxtFile.WriteLine

            For n = 1 To Rng.Rows.Count
                For i = 1 To Rng.Columns.Count
                        TxtFile.Write "|"
                        TxtFile.Write " "
                        TxtFile.Write " "
                        TxtFile.Write Rng.Cells(n, i)

                        For k = 1 To MaxLen - (Len(Rng.Cells(n, i))) - 2
                            TxtFile.Write " "
                        Next k
                Next i
                        TxtFile.Write "|"
                        TxtFile.WriteLine

                For i = 1 To Rng.Columns.Count
                        TxtFile.Write "+"
                            For m = 1 To MaxLen
                                TxtFile.Write "-"
                        Next m
                Next i
                        TxtFile.Write "+"
                        TxtFile.WriteLine
            Next n

        MsgBox "Done!"
        Shell "explorer.exe " & Dpath, vbNormalFocus

End Sub

Now, I have to focus on getting the numbers right aligned. Thank you all for your suggestions.

Export a range of data in excel to ascii plain text formatted table csv or txt file using vba

2 Answers2