How to Extract Comments from a Word Document into Excel

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:

Sample Word Document.docm

Extracted Comments into Excel.xlsx

<< Go Back to Comments in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Sub ExtractCommentsAndSentencesToExcel() Dim xApp As Excel.Application Set xApp = CreateObject("Excel.Application") xApp.Visible = True Dim xWB As Excel.Workbook Set xWB = xApp.Workbooks.Add Dim xWS As Excel.Worksheet Set xWS = xWB.Worksheets(1) xWS.Range("A1:B1").Value = Array("Sentence", "Comment") Dim rowNum As Integer rowNum = 2 Dim doc As Document Set doc = ActiveDocument Dim cmnt As comment For Each cmnt In doc.Comments Dim sentenceText As String sentenceText = cmnt.Scope.Sentences(1).text xWS.Cells(rowNum, 1).Value = sentenceText xWS.Cells(rowNum, 2).Value = cmnt.Range.text rowNum = rowNum + 1 Next cmnt xWS.Columns("A:B").AutoFit Set xWS = Nothing Set xWB = Nothing Set xApp = Nothing End Sub

Sub AdvancedExtractCommentsAndTextWithLinksToExcel() Dim xAPP As Object Dim xWB As Workbook Dim xDoc As Object Dim j As Integer Dim filePath As String filePath = "C:\Users\User\Downloads\AVA (DEMO DOC).docx" Set xAPP = CreateObject("Word.Application") xAPP.Visible = True Set xDoc = xAPP.Documents.Open(filePath) Set xWB = Application.ThisWorkbook With xWB.Worksheets(1) For j = 1 To xDoc.Comments.Count .Cells(j, 1).Formula = xDoc.Comments(j).Initial .Cells(j, 2).Formula = xDoc.Comments(j).Range.Text .Cells(j, 3).Formula = Format(xDoc.Comments(j).Date, "dd/MM/yyyy") If xDoc.Comments(j).Range.Hyperlinks.Count > 0 Then .Cells(j, 4).Formula = xDoc.Comments(j).Range.Hyperlinks(1).Address End If Next j End With xDoc.Close False Set xWB = Nothing Set xDoc = Nothing Set xAPP = Nothing End Sub

Sub AdvancedExtractCommentsAndTextToExcel() Dim xAPP As Object Dim xWB As Workbook Dim xDoc As Object Dim j As Integer Dim filePath As String filePath = "C:\Users\User\Downloads\AVA-DEMO-DOC.docx" Set xAPP = CreateObject("Word.Application") xAPP.Visible = True Set xDoc = xAPP.Documents.Open(filePath) Set xWB = Application.ThisWorkbook With xWB.Worksheets(1) For j = 1 To xDoc.Comments.Count '.Cells(j, 1).Formula = xDoc.Comments(j).Initial .Cells(j, 2).Formula = xDoc.Comments(j).Range.Text .Cells(j, 3).Formula = xDoc.Comments(j).Scope.Text '.Cells(j, 4).Formula = Format(xDoc.Comments(j).Date, "dd/MM/yyyy") Next j End With xDoc.Close False Set xWB = Nothing Set xDoc = Nothing Set xAPP = Nothing End Sub

14 Comments

K Feb 22, 2023 at 2:13 AM

If I have threaded comments how do I adapt the macro to also extract the reply?
- Reply
  Adnan Masruf Feb 22, 2023 at 1:36 PM
  
  Dear K,
  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)
Amir May 23, 2023 at 8:35 PM

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.
- Reply
  Ridwan May 24, 2023 at 4:28 PM
  
  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:
  
  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.

vikashini Jul 6, 2023 at 6:45 AM

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?

Hadi Ul Bashar Jul 9, 2023 at 12:52 PM

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.

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.

Arthur Oct 17, 2023 at 7:16 PM

Hi,

I did everything as detailed here and I get this Error: “Sub or Function Not Defined”.
I would appreciate your help.

Thanks,
Arthur
- Reply
  Nasir Muhammad Munim Oct 18, 2023 at 10:43 AM
  
  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

Ava Dec 5, 2023 at 5:57 PM

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.

Lutfor Rahman Shimanto Dec 6, 2023 at 5:26 PM

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.

1. How to Convert Word to Excel with Columns

2. How to Convert Word to Excel but Keep Formatting

3. How to Import Data from Word to Excel

4. How to Convert Word Table to Excel Spreadsheet

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

Ava Dec 6, 2023 at 6:13 PM

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!

Lutfor Rahman Shimanto Dec 7, 2023 at 11:44 AM

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.

I hope the code presented will make your task easier. Good luck!

JW Oct 23, 2024 at 2:08 AM

This is very helpful, thank you for sharing. I have two questions:
– how to you modify to extract the full name of the commenter in one cell (rather than initials)
– the modification you posted for extracting page numbers doesn’t work for me, tried using this variant but with no success:

.Cells(j, 4).Value = cmt.Scope.Information(wdActiveEndPageNumber)
How would you recommend troubleshooting this? Extracting page numbers with the macro would save hours of manual work
- Reply
  Shamima Sultana Oct 23, 2024 at 7:33 PM
  
  Hello JW,
  
  You are most welcome. Thank you for your feedback! Follow the given steps to solve your queries.
  
  Extracting the full name of the commenter: You can modify your macro to extract the full name by using the .Author property instead of the initials.
  .Cells(j, 2).Value = cmt.Author ‘This extracts the full name of the commenter
  
  Troubleshooting the extraction of page numbers: The issue with extracting page numbers could be related to how the document is structured or the version of Word you’re using. To troubleshoot, try using this code to ensure it’s correctly identifying the page.
  
  .Cells(j, 4).Value = cmt.Scope.Information(wdActiveEndPageNumber)
  
  Double-check that your document has page numbers and that cmt.Scope refers to the correct part of the comment range. You could also try using wdStartOfRange or wdEndOfRange in case the wdActiveEndPageNumber isn’t providing accurate results.
  
  Regards
  ExcelDemy