TAB separated txt file from Excel VBA/MACRO

Question

Hey guys

I need to make an excel file that has a some values in it..into a tab separated values text file.... solution 1 -i can save it as a tab separated text file..but the issue is it also saves the column headers which i dont need.Dont know how to change that !

solution 2--i have this code:

Public Sub CharacterSV()
    Const DELIMITER As String = " |"
    Dim myRecord As Range
    Dim myField As Range
    Dim nFileNum As Long
    Dim sOut As String

    nFileNum = FreeFile
    Open "Test.txt" For Output As #nFileNum
    For Each myRecord In Range("A2:A" & _
                Range("A" & Rows.Count).End(xlUp).Row)
        With myRecord
            For Each myField In Range(.Cells, _
                    Cells(.Row, Columns.Count).End(xlToLeft))
                sOut = sOut & DELIMITER & myField.Text
            Next myField
            Print #nFileNum, Mid(sOut, 2)
            sOut = Empty
        End With
    Next myRecord
    Close #nFileNum
End Sub

Now i can control which cells to tab separate with the "Range" function in my code..however..how do i specify the tab delimiter "Const DELIMITER " instead of the pipe separator? Also..it seems to add the delimeter before the first value (ie from the first cell) how do i make it to start from the after the first value (but include the first cell value in the txt file?)

Answer 1

how do i specify the tab delimiter "Const DELIMITER " instead of the pipe separator?

for the above, you can use vbTab

Answer 2

Also..it seems to add the delimeter before the first value (ie from the first cell) how do i make it to start from the after the first value (but include the first cell value in the txt file?)

This line of code:

sOut = sOut & DELIMITER & myfield.txt

adds the delimiter first, because the first time through your loop, sOut = "". Change this code to:

sOut = sOut & myfield.txt & DELIMITER

Then after your loop has finished, if you don't want the extra delimiter on the end, use a trim operation to remove it.