Convert CommentThreaded and replies to text

Question

I'm using Threaded Comments on my Excel workbooks. Most of them have multiple replies.

I want to convert the comments and replies to text.

I get the first comment using the function CommentThreaded.text but not the replies if any.

How to do it using VBA?

Answer 1

I have found the respond to my question!!!

Here you have some commands to work with threaded comments in VBA:

' To know if a cell has a threaded comment:
    If Not Range("*c,r*").CommentThreaded Is Nothing Then

' To know if the threaded coment has replies:
    Range("*c,r*").CommentThreaded. Replies.Count

' To add a threaded comment:
    Range("*c,r*").AddCommentThreaded ("Add CommentThreaded")

' To response a threaded comment:
    Range("*c,r*").CommentThreaded.AddReply ("*your reply here*")

' To resolve a threaded comment:
    Range("*c,r*").CommentThreaded.Resolved = True

' To re-open a resolved threaded comment:
    Range("*c,r*").CommentThreaded.Resolved = False

' To delete a threaded comment:
    Range("*c,r*").CommentThreaded.Delete

' To get the author:
    Range("*c,r*").CommentThreaded.Author.Name

' To get the date:
    Range("*c,r*").CommentThreaded.Date

' To get the text on the cell where the threaded comment is:
    Range("*c,r*").CommentThreaded.Parent

' To get the address where the threaded comment is:
    Range("*c,r*").CommentThreaded.Parent.Address

' To get the text of the original comment:
    Range("*c,r*").CommentThreaded.Text

' To get the text of the replies:
    Range("*c,r*").CommentThreaded.Replies(*n*).text

' To get the replies author:
    Range("*c,r*").CommentThreaded.Replies(*n*).Author.Name

' To get the date of the reply:
    Range("*c,r*").CommentThreaded.Replies(*n*).Date

I hope you find this guide useful. There is not too much information out there about threaded comments.

Regards,

Answer 2

Bernardo,

Thank you for your list of commands!

I used them to create my function (which also checks for Notes), and I'm sharing it in case anyone is looking for an already-complete solution:

Function UdfComment(rng As Range) As String
'This UDF returns a cell's Note or all its comments (prefixed by Date and Author Name).
'This UDF assumes the range is a single cell.
Application.Volatile
Dim str As String
'First, check for presence of Note (the thing that shows up when you press Shift+F2):
If Not rng.Comment Is Nothing Then
    str = Trim(rng.Comment.Text)
    'If the note has a standard name header on a separate line - if you want to remove it, uncomment this line:
    '(to be safe, can delete the "- 1" at the end to prevent truncating if name header is NOT on a separate line)
    'str = Right(str, Len(str) - InStr(1, str, ":") - 1)
'Notes and Comments seem to be mutually exclusive, so checking for comments in an "Else if":
ElseIf Not rng.CommentThreaded Is Nothing Then
    'First, return the original comment (prefixed by date and author):
    str = rng.CommentThreaded.Date & ", " & rng.CommentThreaded.Author.Name & ":" & vbNewLine & Trim(rng.CommentThreaded.Text)
    'Now check if original comment has replies (if so, iterate through them and append them all to 'str'):
    If rng.CommentThreaded.Replies.Count > 0 Then
        For Each r In rng.CommentThreaded.Replies
            str = str & vbNewLine & r.Date & ", " & r.Author.Name & ":" & vbNewLine & Trim(r.Text)
        Next
    End If
'Without notes and comments, simply return an empty string:
Else
    str = ""
End If

UdfComment = str
End Function

Regards.