将CommentThreaded和回复转换为文本

Question

我在Excel工作簿上使用线程注释。他们中的大多数都有多个回复。

我想将评论和回复转换为文本。

我使用函数CommentThreaded.text得到第一条注释，但没有得到回复(如果有的话(。

如何使用VBA？

Answer 1

以下是一些在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

没有太多关于线程评论的信息，所以我希望这是有用的。

Answer 2

我使用Bernardo的命令列表来创建我的函数(它还检查Notes(，如果有人正在寻找一个已经完整的解决方案，我会分享它：

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