CODEWITHSUNDEEP
excelexport
Raichel
Raichel

Reputation: 41

Exporting Comments from Excel

Is there a way to export the comments from an excel sheet along with the cell value.?

Upvotes: 4

Views: 4008

Answers (1)

Amin1Amin1
Amin1Amin1

Reputation: 103

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

Upvotes: 5

Related Questions