Excel: Extract Comments

Sometimes you need to reply to comments in Excel, but you don't want to use the comment feature because it needs to be documented in a column. This subroutine will extract the comments out to a separate column.

Sub ExtractComments()

  ' Extract comments into a column of your choosing. Combines all comments of a row into a single cell, starting with the ISO date and the initials.

  ' Also returns all replies.

  ' Does not provide any context of the comment in terms of heading, this could be a future feature.

  ' For instance check the filter header or the table column header and add this to the comment.

    Dim cmt As Comment

    Dim rng As Range

    Dim i As Long

    Dim j As Long

    Dim colNum As Long

    Dim initials As String

    Dim reply As CommentThreaded

    calcState = Application.Calculation

    Application.Calculation = xlManual

    Set rng = Application.InputBox("Select a range", "Extract Comments", Selection.Address, Type:=8)

    colNum = Application.InputBox("Select the column where you want to place the comments", "Extract Comments", Type:=8).Column - rng.Column + 1

    For i = 1 To rng.Rows.Count

        rng.Cells(i, colNum).Value = ""

        For j = 1 To rng.Columns.Count

            If Not rng.Cells(i, j).CommentThreaded Is Nothing Then

                rng.Cells(i, colNum).Value = rng.Cells(i, colNum).Value & Chr(10) & Left(rng.Cells(i, j).CommentThreaded.Date, 10) & " " & rng.Cells(i, j).CommentThreaded.Author.Name & ": " & rng.Cells(i, j).CommentThreaded.text

                For Each reply In rng.Cells(i, j).CommentThreaded.Replies

                    rng.Cells(i, colNum).Value = rng.Cells(i, colNum).Value & Chr(10) & Left(reply.Date, 10) & " " & reply.Author.Name & ": " & reply.text

                Next reply

            End If

        Next j

        rng.Cells(i, colNum).Value = Mid(rng.Cells(i, colNum).Value, 2)

    Next i

    Application.Calculation = calcState

End Sub

Also see this thread: https://www.excelforum.com/excel-programming-vba-macros/1370016-vba-to-extract-all-threaded-comments-and-replies-from-a-workbook.html

Handy tip: If your Excel Spreadshet contains old Notes that you wish to convert to the new Excel comments, just run this command:

Review > Notes > Convert to Comments

Or in the Immediate pane:

ActiveWorkbook.ConvertComments