☰

在 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

相關內容