Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 561
  • Last Modified:

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

0
RDLFC
Asked:
RDLFC
  • 4
  • 4
1 Solution
 
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
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
 
RDLFCAuthor Commented:
thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now