Solved

Remove end of line Character from exported memo field

Posted on 2011-02-25
8
552 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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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
 

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

867 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now