Strip chr(13) from Comments

T

tyeholmes

How do you manipulate a variable like a file?

I'm pulling Outlook Task items into a spreadsheet and stuffing the bod
of the Task into the cell Comments. This is working great except th
body of the Task holds formating information like chr(13)s. I want t
read through the Task Body variable in memory and remove the chr(13)
before adding the Comment.

If olTsk.Body <> "" Then
ActiveSheet.Cells(i, x).AddComment olTsk.Body
ActiveSheet.Cells(i, x).Comment.Shape.TextFrame.AutoSize = True
End If

Any ideas?

Thanks,

Ty
 
B

BrianB

Have a look at the CLEAN() worksheet function.

Regards
BrianB
======================================
 
W

Wouter

Hello Tye

Try this:

Dim strComment As String
Dim lngPos As Long
'
' Your code to retreve Outlook info
'
If olTsk.body <> "" Then
strComment = olTsk.body
lngPos = InStr(1, strComment, Chr$(13))
Do While lngPos > 0
Mid$(strComment, lngPos, 1) = Chr$(10)
lngPos = InStr(1, strComment, Chr$(13))
Loop
ActiveSheet.Cells(i, x).AddComment strComment
ActiveSheet.Cells(i, x).Comment.Shape.TextFrame.AutoSize = True
End If

Success,

Wouter
 
D

David McRitchie

Just in case they are Chr(10) perhaps this would be better.
ActiveSheet.Cells(i, x).AddComment _
application.TRIM(replace(replace(olTsk.Body,chr(13)," "),chr(10)," "))

But you definitely don't want to use CLEAN which will remove
characters and not put a space in it's place. More on CLEAN in
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
 
Top