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.