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.
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.
- How to Print Comments in Excel (4 Suitable Ways)
- Find Comments in Excel (4 Easy Methods)
- How to Remove Pop-up Comments in Excel (4 Suitable Ways)
- [Fixed!] Comments in Excel Far Away from Cell (3 Possible Solutions)
- How to Reference Comments in Excel (3 Easy Methods)
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.
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.
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
- 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.
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.
- How to Hide All Comments in Excel (3 Simple Methods)
- Difference Between Threaded Comments and Notes in Excel
- Convert Comments to Notes in Excel (3 Suitable Ways)
- How to Export Instagram Comments to Excel (with Quick Steps)
- Read Full Comment in Excel (6 Handy Methods)
- How to Populate Comment from Another Cell in Excel (and Vice Versa)
- Hide Comments in Excel (4 Quick Methods)