How to Extract Comments from Word Document into Excel

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 a right place. In this article, you learn a handy way to extract comments from Word document into Excel using a VBA code.


Download Practice Workbook

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


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


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 – [An Ultimate Guide]!


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 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 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 (4 Handy Methods)


Conclusion

In this tutorial, I have discussed a handy way to extract comments from Word document into Excel using a VBA code. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. 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.

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

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo