How to Extract Comments from Word Document into Excel

Get FREE Advanced Excel Exercises with Solutions!

If you have several comments in your Word documents, and you want to extract these comments from Word to Excel sheet, then you are in the right place. In this article, you learn a handy way to extract comments from a Word document into Excel using a VBA code.

Table of Contents hide

Steps to Extract Comments from Word Document into Excel

Step 1: Open Word Document

Step 2: Create Comments

Step 3: Open a New Module from Developer Tab

Step 4: Enter VBA Code and Run

Conclusion

Steps to Extract Comments from Word Document into Excel

In order to extract comments from a Word document into Excel using VBA codes, just follow the steps below.

Step 1: Open Word Document

Each cell of the Name column contains comments on the employee.

Similar Readings

Step 2: Create Comments

If you put your cursor on any name in the Name column, the comment on that name will pop up. Our goal is to extract each of these comments into an Excel sheet.

Read More: Creating and Editing Excel Comments to Cells

Similar Readings

Step 3: Open a New Module from Developer Tab

In your Word document, go to Developer tab >> Visual Basic >> Insert >> Module. A module window will appear.

Note:

If you don’t see the Developer tab in the ribbon, then follow the steps below to add the Developer tab to the ribbon.

First, click on the File tab >> Options. A Word Options window will pop up.

Under the Customize Ribbon section, select the Developer option, and finally, click on OK.

Similar Readings

Step 4: Enter VBA Code and Run

Copy the following VBA code and paste it into the module window. Then, click on the Run button.

Sub ExtractCommentsFromWordToExcel()
'Create in Word vba
'set a reference to the Excel object library
Dim xAPP As Excel.Application
Dim xWB As Excel.Workbook
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, then you should go to the Tools tab under the Microsoft Visual Basic for Application Window. Then, click on the References. A References-Project dialog box will appear.

Now, search for the Microsoft Excel 16.0 Object Library option, and select it. And then, click on OK.

Finally, after running the code properly, an Excel workbook will appear with the following results.

Read More: How to Add Comment in Excel

Similar Readings

Download Practice Workbook

You can download the following practice workbook that we have used to prepare this article

Sample Word Document.docm

Extracted Comments into Excel.xlsx

Conclusion

In this tutorial, I have discussed a handy way to extract comments from a Word document into Excel using a VBA code. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.

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

6 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?
- Reply
  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.
```
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
```
  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.

How to Extract Comments from Word Document into Excel

Steps to Extract Comments from Word Document into Excel

Step 1: Open Word Document

Step 2: Create Comments

Step 3: Open a New Module from Developer Tab

Step 4: Enter VBA Code and Run

Conclusion

Related Articles

Leave a reply Cancel reply