Share via

How can I extract comments in excel 365?

Anonymous
2023-10-03T10:54:48+00:00

I have used the Teams version of excel. I then downloaded the spreadsheet and have it in the Office format. I have tried to use various VBA codes to extract the comments from the excel spreadsheet but no luck. Any help would be appreciated.

Microsoft 365 and Office | Excel | For business | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-10-04T08:52:35+00:00

    This one is for comments.

    =======================

    Sub ExtractComments()

    Set cmt = Worksheets(1).CommentsThreaded

    i = 1

    For Each cmt In ActiveSheet.CommentsThreaded

    Range("A" & i).Value = cmt.Author.Name

    Range("B" & i).Value = cmt.Text

    i = i + 1 

     Next cmt

    End Sub

    =======================

    3 people found this answer helpful.
    0 comments
  2. Anonymous
    2023-10-25T15:07:31+00:00

    Thank you Snow Lu,
    This code worked very well!

    PS: I made this simple edit to have the output placed in columns H and I.

    Range("H" & i).Value = cmt.Author.Name

    Range("I" & i).Value = cmt.Text

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2023-10-25T15:16:27+00:00

    You are welcome. You may also give feedback on it.

    0 comments
  4. Anonymous
    2023-10-04T08:41:00+00:00

    Thanks for the assistance, Snow Lu. Unfortunately, it does not seem to work. It might be because with the new comment threads the comments are no longer available next to the linked cell but only as a list of comments. Not sure if that makes sense.

    0 comments
  5. Anonymous
    2023-10-03T11:18:52+00:00

    VBA code to extract comments:

    =========================

    Sub ExtractComments() 

    Dim cmt As Comment 

Dim i As Integer

    i = 1

    For Each cmt In ActiveSheet.Comments

    Range("A" & i).Value = cmt.Author

    Range("B" & i).Value = cmt.Text

    i = i + 1 

    Next cmt

    End Sub

    =========================

    This code will extract the author and text of each comment in the active sheet and place it in columns A and B respectively. You can modify the code to suit your specific needs.

    0 comments