CODEWITHSUNDEEP
vbaexcelread-write
Bramat
Bramat

Reputation: 997

Adding Tab character while exporting Excel files to txt

I wrote a VBA code which takes certain rows from am Excel sheet, and writes them into a TXT file. the code works great, but I have 2 problems -

  1. I've added the "vbTab" character between each 2 cells that I copy, and in the TXT file it looks great - but when I try opening it again in Excel, each row appears in one single cell (text only in Column A), without any spaces, and it doesn't get into the columns like it should be...

  2. In the TXT file, there's quotations (") at the start and end of each row - and it's not good for my use in the TXT files.

Here's the code:

For i = 1 To LastRow
    For j = 1 To LastCol
        If (j = 1) And (Cells(i, 1).Value = "~") Then
            Exit For
        End If
        If Not IsInArray(j, IgnoreCol) Then
            If j = LastCol Then
                DataLine = DataLine + Trim(Cells(i, j).Value)
            Else
                DataLine = DataLine + Trim(Cells(i, j).Value) + vbTab
            End If
        End If
    Next j
    If (DataLine <> "") Then
        Write #FileNum, DataLine
        DataLine = ""
    End If
Next i

Thank you!

Upvotes: 1

Views: 3545

Answers (1)

Alin I
Alin I

Reputation: 590

You should replace:

Write #FileNum, DataLine

with:

Print #FileNum, DataLine

because Write #FileNum, DataLine puts double quotes around the DataLine, so Excel treats entire line as one cell. Print does not include double quotes, so Excel can split the line into cells at each TAB character.

Upvotes: 2

Related Questions