Save xls Sheet as txt file

[Kaizer]

Can you help me with the code to save the xls Sheet as a text file? One thing that I am strugling with is that in the text file the separation between the columns is as tab. I would like to have separation as coma. Can you help?

Thank you in advance.

[Killian]

Hi Kaizer,

There may be a lot of ways to do this but I write to text files a lot so I adapted some code I had already.
It assumes your data Starts at A1 and is continuous (no blank rows) and has the same columns used in each row, so you may have to adjust the range and add some checks for if the cells have data etc

'a constant to set the path where you want to save the files
Const OUTPUT_PATH As String = "C:\Documents and Settings\Killian\Desktop\"

Sub SaveAsText()
'Writes range to comma deleimited text
Dim r As Long, c As Long, r_max As Long, c_max As Long
Dim rng As Range
Dim tempstring As String
Dim iFileNum As Long
With ActiveSheet
    'get the range (all continuous data from cell A1)
        Set rng = .Cells(1, 1).CurrentRegion
        r_max = rng.Rows.Count
        c_max = rng.Columns.Count
For r = 1 To r_max      'loop thru the rows
            tempstring = ""     'reset srting
            For c = 1 To c_max  'for each cell add its value and a comma to the string
                tempstring = tempstring & .Cells(r, c).Value & ","
            Next c
            tempstring = Left(tempstring, Len(tempstring) - 1)  'trim last comma
            tempstring = tempstring & Chr(13)                   'add linefeed
            iFileNum = FreeFile                                 'get file number
            ' build full path from constant + sheet name + .txt
            'if the file does not exist (first loop) it will be created
            Open OUTPUT_PATH & .Name & ".txt" For Append As #iFileNum
            Print #iFileNum, tempstring     'add line to text file                         '
            Close #iFileNum                 'close file
        Next r
    End With
End Sub

[Kaizer]

Thanks a lot Killian. That worked fine for me.