Hi All
I have a query which returns a long text field from a sql database. When I display the text in VB I have "squares" displayed at the start of the each record as well as in various places in the text. I think it has something to with carriage return. I would like my text to be diplayed using the carriage returns but not display the "squares". How can I do this?
Regards
Sam
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Sample code:
Function FixString(Byval inputString as String) as String
Dim strInput As String
strInput = inputString
strInput = "Test line 1" & Chr(10) & "Test line 2" & vbCrLf & _
"Test line 3" & Chr(13) & "Test line 4" & vbCrLf & "Test line 5" & vbCrLf
'Change real crlf into nulls
strInput = Replace(strInput, vbCrLf, Chr(0))
'Change stand-alone line feeds.
strInput = Replace(strInput, Chr(10), vbCrLf)
'Change new crlfs into nulls.
strInput = Replace(strInput, vbCrLf, Chr(0))
' Change standalone carriage returns.
strInput = Replace(strInput, Chr(13), vbCrLf)
'Replace the nulls w/ vbcrlf
strInput = Replace(strInput, Chr(0), vbCrLf)
FixString = strInput
End Sub
I am totally confused now. This is how I am writing out the comment in each box in the following way...
Range("C" & i & ":C" & i).AddComment
Range("C" & i & ":C" & i).Comment.Visible = True
Range("C" & i & ":C" & i).Comment.text text:= rsTest("description")
Range("C" & i & ":C" & i).Comment.Shape.TextFrame
My SQL Database is storing "description" as longtext and the vaules in this field has carriage returns as this helps to span out the comments so thay are visible easily. The problem is when I write the line out I get "squares" written in comments.
So that you understand:
The characters in your string cannot be understood by Excel. That is why they are printing out as "squares"
To resolve this you need to ensure that such characters are either removed or changed before displaying in Excel, or (even better) that they are not added in to the data in the first place.
In one of your postings you wrote:
"the database holds text including carriage returns"
If this is true, then this is your problem. Excel will not understand carriage returns by themselves. Ensure that when the data is placed into the database, new lines are indicated by a carriage return immediately followed by a line feed. As pointed out by Pi7, this is done (in VB or VBA) by using the constant vbCrLf
What application is being used to insert the data (with the carriage returns) into the database?
Hi,
Saw this post, and mind me saying, but this is simple to resolve. The problem with the "square's" in your text is as others have said. Excel doesn't understand the combination LineFeed and CariageReturn in comment text.
To resolve this problem, taking you use VB6, is to replace all instances of vbCrLf with CHR$(10) = vbLf. Excel then should have no problem with it.
The following code does this:
Range("C" & i & ":C" & i).Comment.text text:= Replace(rsTest("descriptio
If you still get square's, then there are other character codes you need to strip.
Thus, to recap:
- Excel comments don't understand cariage returns.
- Excel comments do understand line feeds.
- In order to use multiline comments in Excel, use linefeeds in your strings.
Grtz.©
D.
-- Don't shoot, I'm already dead.
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
No response from demmick from 2/8/2004 comment
Award points to daffyduck14mil(50%) and Cronan(50%) is recommend.
Please leave any comments here within the next four days.
Due Date : 3/1/2004
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
planocz
EE Cleanup Volunteer
Business Accounts
Answer for Membership
by: Pi7Posted on 2003-12-16 at 06:49:27ID: 9949490
Hi there,
whenever you want to display text on 2 different lines use the vb const vbcrlf
e.g. if i want the text "This is an example.Hope it works fine" to appear in a textbox or label control like
This is an example.
Hope it works fine
This is what I'll do assuming the text will be displayed in a text box
text1.text = "This is an example." & vbCrlf & "Hope it works fine"