Remove end of line Character from exported memo field

I am outputing a memo filed from access to excel.  The transfer from access to excel is sucessful but when I open the excel file the memo field had end of line characters in it.  Is there a way to remove these characters when exporting?
Do Until rst.EOF
     For iCol = 0 To rst.Fields.Count - 1
        .cells(iRow, iCol + 1).Value = CStr(Nz(rst.Fields(iCol), ""))
     Next
    iRow = iRow + 1
rst.MoveNext
Loop

Open in new window

RDLFCAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
try this one first

Do Until rst.EOF
     For iCol = 0 To rst.Fields.Count - 1
      .cells(iRow, iCol + 1).Value = replace(CStr(Nz(rst.Fields(iCol), "")),chr(13),"")

     Next
    iRow = iRow + 1
rst.MoveNext
Loop
0
 
Rey Obrero (Capricorn1)Commented:
test this

Do Until rst.EOF
     For iCol = 0 To rst.Fields.Count - 1
        .cells(iRow, iCol + 1).Value = CStr(Nz(replace(replace(replace(rst.Fields(iCol),chr(13) & chr(10),""),chr(13),""),chr(10),"") , ""))
     Next
    iRow = iRow + 1
rst.MoveNext
Loop
0
 
RDLFCAuthor Commented:
when i apply this i receive an invalid use of Null error
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Rey Obrero (Capricorn1)Commented:
how about this

Do Until rst.EOF
     For iCol = 0 To rst.Fields.Count - 1
      .cells(iRow, iCol + 1).Value = replace(replace(replace(CStr(Nz(rst.Fields(iCol), "")),chr(13) & chr(10),""),chr(13),""),chr(10),"")

     Next
    iRow = iRow + 1
rst.MoveNext
Loop
0
 
RDLFCAuthor Commented:
the code works now but it removes all the end of lines.   What I wanted remove was a computer generated character that appears in the excel sheet.  see attached file.
Doc1.doc
0
 
Rey Obrero (Capricorn1)Commented:
i need to see the excel file to see what those characters are
0
 
RDLFCAuthor Commented:
see excel file attached.
example.xls
0
 
RDLFCAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.