October 5, 2012

Techbits #23: Export comments from Word document

There could be scenarios where one encounters the need to export/extract all the comments from a word document into - say, a word document or an excel sheet. This techbit is precisely going to address such usecase.

All that you would need to do is define the following Macros and just use (run) them afterwards. The Macros can be seen by following the Word document's menu path "View >> Macros >> View Macros".

Exporting comments into an Excel file

Create a Macro named exportCommentsIntoExcel with the following definition:
Sub exportCommentsIntoExcel()

Dim cmt As Word.Comment
Dim doc As Word.Document
Dim commentsFile As String
Dim row As Integer

commentsFile = ActiveDocument.FullName & " - Comments.xls"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add()

row = 1

For Each cmt In ActiveDocument.Comments
    objExcel.Cells(row, 1).Value = cmt.Initial & cmt.Index
    objExcel.Cells(row, 2).Value = cmt.Range.Text
    row = row + 1
Next

objWorkbook.SaveAs (commentsFile)

End Sub
Select the Macro thus created and "Run" it.

The comments should be available in an excel file in the same directory as the source word document and with the suffix "- Comments" in its name.

Exporting comments into a Word document

Create a Macro named exportCommentsIntoWord with the following definition:

Sub exportCommentsIntoWord()

Dim s As String
Dim cmt As Word.Comment
Dim doc As Word.Document

commentsFile = ActiveDocument.FullName & " - Comments.doc"

For Each cmt In ActiveDocument.Comments
    s = s & cmt.Initial & cmt.Index & "," & cmt.Range.Text & vbCr
Next

Set doc = Documents.Add
doc.Range.Text = s
doc.SaveAs (commentsFile)
End Sub
Select the Macro thus created and "Run" it.

The comments should be available in a word document in the same directory as the source word document and with the suffix "- Comments" in its name.

2 comments:

Anonymous said...

I used the Excel to Word macro, and it worked perfectly! It is also much simpler than some of the other macros I have seen posted in other forums to answer this question. Thanks for the nice work!

Anonymous said...

Much simpler code to extract word comment to excel. Fantastic

I do not know whether it is possible to add on it or not is the actual text and the page number where the comment is made in the word document.