Strip chr(13) from Comments
 

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

--
Message posted from http://www.ExcelForum.com

Strip chr(13) from Comments
 

ActiveSheet.Cells(i, x).AddComment replace(olTsk.Body,chr(13)," ")

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"tyeholmes " wrote in message ...
How do you manipulate a variable like a file?

I'm pulling Outlook Task items into a spreadsheet and stuffing the body
of the Task into the cell Comments. This is working great except the
body of the Task holds formating information like chr(13)s. I want to
read through the Task Body variable in memory and remove the chr(13)s
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,

Tye


---
Message posted from http://www.ExcelForum.com/

Strip chr(13) from Comments
 

Have a look at the CLEAN() worksheet function.

Regards
BrianB
======================================



tyeholmes wrote in message ...
How do you manipulate a variable like a file?

I'm pulling Outlook Task items into a spreadsheet and stuffing the body
of the Task into the cell Comments. This is working great except the
body of the Task holds formating information like chr(13)s. I want to
read through the Task Body variable in memory and remove the chr(13)s
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,

Tye


---
Message posted from http://www.ExcelForum.com/

Strip chr(13) from Comments
 

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


tyeholmes wrote in message ...
How do you manipulate a variable like a file?

I'm pulling Outlook Task items into a spreadsheet and stuffing the body
of the Task into the cell Comments. This is working great except the
body of the Task holds formating information like chr(13)s. I want to
read through the Task Body variable in memory and remove the chr(13)s
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,

Tye


---
Message posted from http://www.ExcelForum.com/

Strip chr(13) from Comments
 

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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm