Extract comments (Not notes) from excel

[IamLost]

Hello, I have looked through the search and cant find anything, also google is not helping. I have inherited an excel spreadsheet which has a lot of data as well as a lot of comments. I am looking for a way to extract the comment into a new cell. Everything I read online refers to the "old" comments which are now called Notes I believe.

To simplify, I have a spreadsheet with for e.g. Column A has information in the cell as well as a comment. What I want to be able to do is run a macro to extract or copy the information in the comment and paste this in the cell to the right.

Any help would be much appreciated.

 

[Fluff]

When you try the code does it work on your version?

Yup, the L3 is just a single comment & N7 has a reply.

 

[IamLost]

You gave a thumbs up on my answer so did my answer work?

Thumbs up as a thank you. The code works for the Notes but unfortunately, not comments.

 

[My Aswer Is This]

Thumbs up as a thank you and works for the Nots not comments.

Well my script was written for comments not Notes. I use Excel 2013 and am not familiar with Notes. So it's surprising my script works for Notes but not comments.

 

[Fluff]

@My Aswer Is This
The Comments you are referring are now called Notes (from 2016 onwards), which is why your code worked for them. What are now called Comments are something completely different.

 

[IamLost]

Well my script was written for comments not Notes. I use Excel 2013 and am not familiar with Notes. So it's surprising my script works for Notes but not comments.

There is obviously a bit of confusion, but as explained by Fluff.

Older versions of Excel called them Comments, These are now called Notes - This is what your code works on
In new versions of Excel, they created a new feature and called them comments. These are NOT the same as the old comments, but a new feature where you comment and respond on comments.

 

[Zot]

Well my script was written for comments not Notes. I use Excel 2013 and am not familiar with Notes. So it's surprising my script works for Notes but not comments.

Here is explanation from MS

The difference between threaded comments and notes - Microsoft Support

Comments and notes are new in Excel for Office 365. This article explains the differences.

support.microsoft.com

 

[Ccox]

In case this is still relevant for anybody, this VBA code works for me for extracting the newer-style comments rather than old-style comments (now notes). It then dumps these into a new worksheet called 'Comments'

Sub ExtractThreadedComments()
    Dim ws As Worksheet
    Dim threadedCmt As CommentThreaded
    Dim newWs As Worksheet
    Dim rowNum As Integer
    
    ' Set reference to Sheet1
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Add a new worksheet for threaded comments
    Set newWs = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    newWs.Name = "Threaded Comments"
    
    ' Initialize row number
    rowNum = 1
    
    ' Loop through each threaded comment
    For Each threadedCmt In ws.CommentsThreaded
        newWs.Cells(rowNum, 1).Value = "Threaded Comment: " & threadedCmt.Text
        rowNum = rowNum + 1
        ' Loop through replies in the threaded comment
        For i = 1 To threadedCmt.Replies.Count
            newWs.Cells(rowNum, 1).Value = "Reply: " & threadedCmt.Replies(i).Text
            rowNum = rowNum + 1
        Next i
    Next threadedCmt
End Sub