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 RemoveCarriageR eturn_UMG()
Set fso = CreateObject("S cripting.FileSy stemObject")
Set NewFile = fso.CreateTextF ile("C:\hello.t xt")
Set file = fso.OpenTextFil e("C:\return.tx t")
Do While Not file.AtEndOfStr eam
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.goo glegroups.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 RemoveCarriageR eturn_UMG()
Set fso = CreateObject("S cripting.FileSy stemObject")
Set NewFile = fso.CreateTextF ile("C:\hello.t xt")
Set file = fso.OpenTextFil e("C:\return.tx t")
Do While Not file.AtEndOfStr eam
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("S cripting.FileSy stemObject")

'1 means for reading

inputFile = fso.OpenTextFil e("c:\return.tx t", 1)

str = inputFile.ReadA ll

'modify this string, replace required characters

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

'write back

outputFile = fso.CreateTextF ile("c:\bleh.tx t", True)

outputFile.Writ e (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.goo glegroups.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 RemoveCarriageR eturn_UMG()
Set fso = CreateObject("S cripting.FileSy stemObject")
Set NewFile = fso.CreateTextF ile("C:\hello.t xt")
Set file = fso.OpenTextFil e("C:\return.tx t")
Do While Not file.AtEndOfStr eam
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