Step 1 – Open Word Document
- Each cell of the Name column contains comments on the employee.
Step 2 – Create Comments
- If you put your cursor on any name in the Name column, the comment associated with that name will pop up.
- Our goal is to extract each of these comments into an Excel sheet.
Step 3 – Open a New Module from Developer Tab
- In your Word document, go to the Developer tab.
- Click on Visual Basic > Insert > Module. A module window will appear.
Note:
If you don’t see the Developer tab in the ribbon, follow these steps to add it:
- Click on the File tab > Options.
- In the Word Options window, under the Customize Ribbon section, select the Developer option, and click OK.
Step 4 – Enter VBA Code and Run
- Copy the following VBA code and paste it into the module window.
- Click on the Run button.
Sub ExtractCommentsFromWordToExcel()
'Create in Word vba
'set a reference to the Excel object library
Dim xAPP As Object
Dim xWB As Object
Dim j As Integer
Set xAPP = CreateObject("Excel.Application")
xAPP.Visible = True
Set xWB = xAPP.Workbooks.Add ' create a new workbook
With xWB.Worksheets(1)
For j = 1 To ActiveDocument.Comments.Count
.Cells(j, 1).Formula = ActiveDocument.Comments(j).Initial
.Cells(j, 2).Formula = ActiveDocument.Comments(j).Range
.Cells(j, 3).Formula = Format(ActiveDocument.Comments(j).Date, "dd/MM/yyyy")
Next j
End With
Set xWB = Nothing
Set xAPP = Nothing
End Sub
Note: If the code doesn’t run, follow these additional steps:
- Go to the Tools tab under the Microsoft Visual Basic for Applications window.
- Click on References.
- In the References-Project dialog box, search for the Microsoft Excel 16.0 Object Library option and select it. Click OK.
After running the code, an Excel workbook will appear with the extracted comments.
Read More: How to Extract Comments in Excel
Download Practice Workbook
You can download the practice workbooks from here:
Related Articles
- How to Populate Comment from Another Cell in Excel
- How to Export Instagram Comments to Excel
- Convert Comments to Notes in Excel
- [Fixed!] Comments in Excel Far Away from Cell
- [Fixed!] Excel Comment Only Showing Arrow
- [Solved!] Comments Are Not Displaying Properly in Excel
- Difference Between Threaded Comments and Notes in Excel
<< Go Back to Comments in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
If I have threaded comments how do I adapt the macro to also extract the reply?
Dear K,
![](https://www.exceldemy.com/wp-content/uploads/2022/06/reply.png)
![](https://www.exceldemy.com/wp-content/uploads/2022/06/reply2.png)
Thanks for your query. If we add replies to the comments (Like the first picture), we can find the replies by simply looking at the initials without changing the macros (Like the second picture)
Hi there,
I was wondering if it’s possible to extract a location index for each comment such as page number or the section name where the comment exists. But I couldn’t find such property from “ActiveDocument.Comments” in word. I’d appreciate if you know how could this be added to the code.
Thanks a lot.
Hello AMIR,
Hope you are doing well and thank you for your query. You can extract comments from word document with their respective locations in an Excel sheet. For this, you can use the following code:
Sub ExtractCommentsFromWordToExcel()
Dim cmt As Comment
Dim j As Long: j = 1
Dim xAPP As Excel.Application
Dim xWB As Excel.Workbook
Set xAPP = CreateObject(“Excel.Application”)
xAPP.Visible = True
Set xWB = xAPP.Workbooks.Add
With xWB.Worksheets(1)
For Each cmt In ActiveDocument.Comments
.Cells(j, 1).Value = cmt.Range.Text
.Cells(j, 2).Value = “Page ” & cmt.Scope.Information(wdActiveEndPageNumber) & “, Section ” & cmt.Scope.Sections(1).Range.Information(wdActiveEndSectionNumber)
.Cells(j, 3).Value = Format(cmt.Date, “dd/MM/yyyy”)
j = j + 1
Next cmt
End With
Set xWB = Nothing
Set xAPP = Nothing
End Sub
Here is an image displaying the result in Excel sheet:
![](https://www.exceldemy.com/wp-content/uploads/2023/05/rps7-1.png)
![](https://www.exceldemy.com/wp-content/uploads/2023/05/rps7-2.png)
Hope you find this useful. Have a good day. Please let us know if you have any further queries. Also, you can post your Excel-related problems in the ExcelDemy Forum with images or Excel workbooks.
Hi,
I am an editor and the customers I work for insert comments on a particular word in a sentence. So I need to have a look on that complete sentence. Is it possible to extract the whole sentence?
Hello VIKASHINI,
Thank you for explaining your issue in a concise way. Yes, it is possible to extract the whole sentence. You can apply the following steps for that:
1. First of all, open your Word document.
2. Go to Developer tab and select Visual Basic.
3. Navigate to Insert tab and select Module.
4. Type the following code in the module and press the F5 key to run the macro.
Check the following images that contain the sample output.
![](https://www.exceldemy.com/wp-content/uploads/2022/06/word-containing-comment.png)
![](https://www.exceldemy.com/wp-content/uploads/2022/06/sentences-containing-the-comments.png)
This is the output you will get in an Excel file.
Please let me know if you have any further issues.
Have a good day!
Regards,
Bashar
Exceldemy.
Hi,
I did everything as detailed here and I get this Error: “Sub or Function Not Defined”.
I would appreciate your help.
Thanks,
Arthur
Hi Arthur,
Thank you for finding out about this issue. We have updated the VBA code to avoid the “Sub or Function Not Defined” error. Please try again with this new code and let us know if this problem still exists or not.
Regards,
Team Exceldemy
Hi,
I need help with extracting comments and text selected linked with the comment (from Word to Excel). Is it possible to do so? Please help me with this.
And also where can I learn to do this?
Thanks a lot.
Hello AVA
Thanks for reaching out and posting an exciting comment. You wanted to extract comments besides the links within the comment from Word to Excel.
I am delighted to inform you that the requirement you describe can be met with the help of Excel VBA. I am presenting a sub-procedure that will fulfil your goal.
Excel VBA Sub-procedure:
OUTPUT OVERVIEW:
To learn more you can read the following articles.
You can download the Solution Workbook and Word Document for better understanding.
Download Excel Workbook
Download Word Document
I hope the given code and resources will help. Stay blessed.
Regards
Lutfor Rahman Shimanto
ExcelDemy
Hi,
Your code and resources are very useful, but I apologize if I didn’t make it clear.
What I mean is simply a column of comments and a column of selected texts
(e.g. Josef Albers | Artist). I’d appreciate your help with this
Thank you so much Mr.Lutfor Rahman Shimanto!
Dear AVA
Your appreciation means a lot to us. Thanks for describing your problem with such clarity. You want a column of comments and a column of texts in which a comment was made (e.g. Josef Albers | Artist).
I am delighted to let you know that I modified the previous code to fulfil your requirements.
Excel VBA Sub-procedure:
OUTPUT OVERVIEW:
I hope the code presented will make your task easier. Good luck!
Regards
Lutfor Rahman Shimanto
ExcelDemy