control-M characters in text files output from Excel

Hi everyone

I have a VBA program which writes out text files using the "print" and/or "write" statements. When I read these text files on a Unix system, there are ^M (control-M) characters at the end of each line. I know that there are ways to remove these type of characters from a file which is read INTO Excel ("clean" command, rigth?) But is there a way to make sure that the text files written OUT of excel does not contain them?

I know it is fairly straightforward to remove the characters using "vi" on Unix, but I'm trying to remove the need for this extra step. Is there a way to keep the ^Ms out of the files to begin with?

Thanks!
Emma

I understand that Ctrl-M is a carriage return character (ASCII 13).

You could continue to use Print #, sending the output to the file once as one long string, removing Ctrl-M characters with Instr from each piece of the string as you go along.

Or for a more flexibility, use the FileSystemObject object, see code example below.

Sub Output2()
'set a reference to the Microsoft Scripting Runtime (Tools > References)
Dim fso As FileSystemObject
Dim ts As TextStream
Dim strOutput As String

strOutput = "hello world"
strOutput = strOutput & Chr(13) 'insert Ctrl-M

If InStr(strOutput, Chr(13)) Then 'strip Ctrl-M
strOutput = Left(strOutput, Len(strOutput) - 1)
End If

Set fso = New FileSystemObject

Set ts = fso.CreateTextFile("c:\documents and settings\username\my documents\TESTFILE2.txt", True)
ts.writeline ("This is a test.")
ts.writeline ("line 2")
ts.Write strOutput
ts.Write " more text"
ts.Write " on same line"

ts.Close
End Sub

Hi there

Thank you so much for the reply. The FileSystemObject turned out to be really useful. It is a much cleaner way of writing to the file. Although it didn't completely solve my problem. The problem is that I WANT a new line, but not the Control-M characters.

I found out from browsing some other websites that if I type

print #fileNum, "my text" & vbLf;

instead of the usual

print #fileNum, "my text"

it uses a "vbLf" instead of "vbCrLf" at the end of each line. When these files are later read on a UNIX system, the Control-Ms are no longer there, but I still have a new line after each print statement.

Emma