Export comments from Excel

Question

Is there any way to export comments from excel sheet along with cell value.?

Answer 1

Here is an example of a function that returns all comments in a sheet:

Sub CreateCommentsSummary()
    Dim rgComments As Range, rgCell As Range, rgOutput As Range

    ' get all cells with comment
    Set rgComments = ActiveSheet.Cells.SpecialCells(xlCellTypeComments)

    ' get cell reference where user want to place the summary
    Set rgOutput = _
        Application.InputBox(Prompt:="Select cell where you want to put the comments summary", _
        Title:="Comments Summary", Type:=8)

    ' read each cell with comment and build the summary
    For Each rgCell In rgComments
        rgOutput.Range("A1") = rgCell.Address    ' print cell address
        rgOutput.Offset(0, 1).Range("A1") = rgCell.Value   ' print cell value
        rgOutput.Offset(0, 2).Range("A1") = rgCell.comment.Text    'print cell comment text
        Set rgOutput = rgOutput.Offset(1, 0)
    Next rgCell
End Sub