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.


How to Extract Comments from Word Document into Excel: Step by Step Procedure

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


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


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


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

VBA code to extract comments from Word to 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 Extract Comments in Excel


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


<< Go Back to Comments in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hafizul Islam
Hafizul Islam

Hafizul Islam is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role,... Read Full Bio

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

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

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

    • Reply Lutfor Rahman Shimanto
      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:

      
      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
      

      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

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

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

          Excel VBA Sub-procedure:

          
          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
          

          OUTPUT OVERVIEW:

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

          Regards
          Lutfor Rahman Shimanto
          ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo