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.


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.

Extract Comments from Word Document into Excel

Read More: [Fixed!] Excel Comment Only Showing Arrow


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.

Extract Comments from Word Document into Excel

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.

Extract Comments from Word Document into Excel

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.

Extract Comments from Word Document into Excel

Read More: How to Filter Cells with Comments in Excel


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

VBA Code to Extract Comments from Word Document into Excel

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.

Extract Comments from Word Document into Excel

  • 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


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.


Related Articles

Hafiz Islam
Hafiz Islam

Hi there. I am Hafiz, graduated from BUET. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. Now you can see my articles in the ExcelDemy blog.

6 Comments
  1. If I have threaded comments how do I adapt the macro to also extract the reply?

    • 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)

  2. 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:

      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.

  3. 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.

      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.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo