Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Remove end of line Character from exported memo field

Posted on 2011-02-25
8
554 Views
Last Modified: 2012-05-11
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
Comment
Question by:RDLFC
  • 4
  • 4
8 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34982531
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
 

Author Comment

by:RDLFC
ID: 34982705
when i apply this i receive an invalid use of Null error
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34982778
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:RDLFC
ID: 34982946
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34983003
i need to see the excel file to see what those characters are
0
 

Author Comment

by:RDLFC
ID: 34983036
see excel file attached.
example.xls
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 34983310
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
 

Author Comment

by:RDLFC
ID: 34983335
thanks
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question