export word review comments in excel

Its way late, but this was SUPER helpful. Saved me so much time. Thanks!

Try this to get references to the numbered headings that go with the comments.:

Sub exportComments()

' Exports comments from a MS Word document to Excel and associates them with the heading paragraphs

' they are included in. Useful for outline numbered section, i.e. 3.2.1.5....

' Thanks to Graham Mayor, http://answers.microsoft.com/en-us/office/forum/office\_2007-customize/export-word-review-comments-in-excel/54818c46-b7d2-416c-a4e3-3131ab68809c

' and Wade Tai, http://msdn.microsoft.com/en-us/library/aa140225(v=office.10).aspx

' Need to set a VBA reference to "Microsoft Excel 14.0 Object Library"

Dim xlApp As Excel.Application

Dim xlWB As Excel.Workbook

Dim i As Integer, HeadingRow As Integer

Dim objPara As Paragraph

Dim objComment As Comment

Dim strSection As String

Dim strTemp

Dim myRange As Range

Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True

Set xlWB = xlApp.Workbooks.Add 'create a new workbook

With xlWB.Worksheets(1)

' Create Heading

HeadingRow = 1

.Cells(HeadingRow, 1).Formula = "Comment"

.Cells(HeadingRow, 2).Formula = "Page"

.Cells(HeadingRow, 3).Formula = "Paragraph"

.Cells(HeadingRow, 4).Formula = "Comment"

.Cells(HeadingRow, 5).Formula = "Reviewer"

.Cells(HeadingRow, 6).Formula = "Date"

strSection = "preamble" 'all sections before "1." will be labeled as "preamble"

strTemp = "preamble"

If ActiveDocument.Comments.Count = 0 Then

MsgBox ("No comments")

Exit Sub

End If

For i = 1 To ActiveDocument.Comments.Count

Set myRange = ActiveDocument.Comments(i).Scope

strSection = ParentLevel(myRange.Paragraphs(1)) ' find the section heading for this comment

'MsgBox strSection

.Cells(i + HeadingRow, 1).Formula = ActiveDocument.Comments(i).Index

.Cells(i + HeadingRow, 2).Formula = ActiveDocument.Comments(i).Reference.Information(wdActiveEndAdjustedPageNumber)

.Cells(i + HeadingRow, 3).Value = strSection

.Cells(i + HeadingRow, 4).Formula = ActiveDocument.Comments(i).Range

.Cells(i + HeadingRow, 5).Formula = ActiveDocument.Comments(i).Initial

.Cells(i + HeadingRow, 6).Formula = Format(ActiveDocument.Comments(i).Date, "dd/MM/yyyy")

.Cells(i + HeadingRow, 7).Formula = ActiveDocument.Comments(i).Range.ListFormat.ListString

Next i

End With

Set xlWB = Nothing

Set xlApp = Nothing

End Sub

Function ParentLevel(Para As Word.Paragraph) As String

'From Tony Jollans

' Finds the first outlined numbered paragraph above the given paragraph object

Dim ParaAbove As Word.Paragraph

Set ParaAbove = Para

sStyle = Para.Range.ParagraphStyle

sStyle = Left(sStyle, 4)

If sStyle = "Head" Then

GoTo Skip

End If

Do While ParaAbove.OutlineLevel = Para.OutlineLevel

Set ParaAbove = ParaAbove.Previous

Loop

Skip:

strTitle = ParaAbove.Range.Text

strTitle = Left(strTitle, Len(strTitle) - 1)

ParentLevel = ParaAbove.Range.ListFormat.ListString & " " & strTitle

End Function

thank you so much!!! code worked perfectly and saved me a lot of time!

Maybe something like

Sub CopyCommentsToExcel()

'Create in Word vba

'set a reference to the Excel object library

Dim xlApp As Excel.Application

Dim xlWB As Excel.Workbook

Dim i As Integer

Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True

Set xlWB = xlApp.Workbooks.Add ' create a new workbook

With xlWB.Worksheets(1)

    For i = 1 To ActiveDocument.Comments.Count

        .Cells(i, 1).Formula = ActiveDocument.Comments(i).Initial

        .Cells(i, 2).Formula = ActiveDocument.Comments(i).Range

        .Cells(i, 3).Formula = Format(ActiveDocument.Comments(i).Date, "dd/MM/yyyy")

    Next i

End With

Set xlWB = Nothing

Set xlApp = Nothing

End Sub

http://www.gmayor.com/installing_macro.htm

<Rrrrrrrgrrrrrr> wrote in message news:*** Email address is removed for privacy ***...

Hi , I have several Review comments in my word documents, I wan to export all these comments to excel sheet.

If it is possible by macros, please guide me with step by step process.

Regards,

RG

Graham Mayor - Word MVP

www.gmayor.com

Posted via the Communities Bridge

http://communitybridge.codeplex.com/