☰

在 Excel 中列出注释

tag-icon • tag-icon microsoft-excel
在 Excel 中列出注释

如何在一个选项卡上列出工作表上的所有评论？

我知道这是可能的，但我不知道该怎么做。我想打印工作表上包含的所有注释的一页以方便使用。有什么想法吗？

答案1

我已经有一个函数几乎可以完成您要求的功能，因此只需稍作调整即可。此宏 ( listComments2) 应该可以解决问题。

Sub listComments2()
'Thanks to http://superuser.com/a/809212/529100
Dim commentWS As Worksheet

Application.ScreenUpdating = False

Set commentWS = ActiveWorkbook.Worksheets.Add(after:=ActiveWorkbook.Worksheets(Sheets.Count))
commentWS.Name = "Comments"

Dim cmts As New Collection

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Comments" Then
        For Each cmt In ws.Comments
            cmts.Add cmt.Text
        Next cmt
    End If
Next ws

Dim commentArray() As Variant
commentArray = toArray(cmts)

Dim i As Long
For i = LBound(commentArray) To UBound(commentArray)
    With commentWS
        .Cells(i, 1).Value = commentArray(i)
    End With
Next i

' Reformat columns/worksheet
With commentWS.Cells
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    .EntireColumn.AutoFit
End With

Application.ScreenUpdating = True

End Sub


Function toArray(cmt As Collection)

Dim i As Long
Dim arr() As Variant
ReDim arr(1 To cmt.Count) As Variant
For i = 1 To cmt.Count
    arr(i) = cmt(i)
Next i

toArray = arr
End Function

相关内容