Remove Carriage Return in Access Exported Text File

[Ellen]

Hi All -

I have some data that I export from Access to a pipe-delimited text
file. Unfortunately, the text file always has a carriage return symbol
at the end. I need to export this file frequently and send to a system
that cannot accept this character at the end of the file. I am looking
for a way, perhaps with VB code, to automate this process.

I found this code on another Google Group, but I can't get it to work.
Any advice appreciated. :O)

Here is that code:

Function RemoveCarriageReturn_UMG()
Set fso = CreateObject("Scripting.FileSystemObject")
Set NewFile = fso.CreateTextFile("C:\hello.txt")
Set file = fso.OpenTextFile("C:\return.txt")
Do While Not file.AtEndOfStream
CLine = file.ReadLine
CLine = Replace(CLine, vbCrLf, "")
NewFile.Write CLine
Loop

file.Close
NewFile.Close
Set NewFile = Nothing
Set file = Nothing
Set fso = Nothing
End Function

[Paradigm]

You need to search for chr(10) and chr(13) and replace them

"Ellen" <el***********@gmail.comwrote in message
news:11**********************@m58g2000cwm.googlegr oups.com...

Hi All -

I have some data that I export from Access to a pipe-delimited text
file. Unfortunately, the text file always has a carriage return symbol
at the end. I need to export this file frequently and send to a system
that cannot accept this character at the end of the file. I am looking
for a way, perhaps with VB code, to automate this process.

I found this code on another Google Group, but I can't get it to work.
Any advice appreciated. :O)

Here is that code:

Function RemoveCarriageReturn_UMG()
Set fso = CreateObject("Scripting.FileSystemObject")
Set NewFile = fso.CreateTextFile("C:\hello.txt")
Set file = fso.OpenTextFile("C:\return.txt")
Do While Not file.AtEndOfStream
CLine = file.ReadLine
CLine = Replace(CLine, vbCrLf, "")
NewFile.Write CLine
Loop

file.Close
NewFile.Close
Set NewFile = Nothing
Set file = Nothing
Set fso = Nothing
End Function

[Ellen]

Thanks Paradigm... I have just figured that out... (I'm not a
programmer, can you tell?)

Anyhow, I'm trying to use this in Access, but when I try to name it as
a Function it won't work...

Sorry to bother again... Any clue?
Dim fso, inputFile, outputFile
Dim str As String

fso = CreateObject("Scripting.FileSystemObject")

'1 means for reading

inputFile = fso.OpenTextFile("c:\return.txt", 1)

str = inputFile.ReadAll

'modify this string, replace required characters

str = Replace(str, "Char(10)", "")

'write back

outputFile = fso.CreateTextFile("c:\bleh.txt", True)

outputFile.Write (str)
Paradigm wrote:

You need to search for chr(10) and chr(13) and replace them

"Ellen" <el***********@gmail.comwrote in message
news:11**********************@m58g2000cwm.googlegr oups.com...

Hi All -

I have some data that I export from Access to a pipe-delimited text
file. Unfortunately, the text file always has a carriage return symbol
at the end. I need to export this file frequently and send to a system
that cannot accept this character at the end of the file. I am looking
for a way, perhaps with VB code, to automate this process.

I found this code on another Google Group, but I can't get it to work.
Any advice appreciated. :O)

Here is that code:

Function RemoveCarriageReturn_UMG()
Set fso = CreateObject("Scripting.FileSystemObject")
Set NewFile = fso.CreateTextFile("C:\hello.txt")
Set file = fso.OpenTextFile("C:\return.txt")
Do While Not file.AtEndOfStream
CLine = file.ReadLine
CLine = Replace(CLine, vbCrLf, "")
NewFile.Write CLine
Loop

file.Close
NewFile.Close
Set NewFile = Nothing
Set file = Nothing
Set fso = Nothing
End Function