export word review comments in excel

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

---

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/

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

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

 

Hi Graham,

Thank you for your VB code, but it won't work on my computer. (Window 7, MS 2010)

Once I run the macro, it gave me the error "Compile error: User-defined type not defined", and the line "Excel.Application" was highlighted.

 

Woud you please tell me how to fix it?

I'm new to Macro and I'm almost certain it's me who did something wrong with it.

 

Thank you very much!

Julia

At the top of the macro you will see the instruction

Sub CopyCommentsToExcel()
'Create in Word vba
'set a reference to the Excel object library

This means that from the VBA editor you should select Tools > References and check the box against Microsoft Excel. Otherwise Word will not know what to do with Excel commands

This is very helpful, but i'm getting an error:

Run-time error '91'

Object variable or With block variable not set

 

This is the offending line:

sStyle = Para.Range.ParagraphStyle

 

any help?

 

Kaye

Hi I'm getting this same Run-time error '91'

My original macro still works, but perhaps could do with changing to late binding thus:

Sub CopyCommentsToExcel()
'Create in Word vba
Dim xlApp As Object
Dim xlWB As Object
Dim i As Integer
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err Then
        Set xlApp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
    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


The error relates to aldoDuke's addition of Function ParentLevel(Para As Word.Paragraph) As String which I have not evaluated.

My original macro still works, but perhaps could do with changing to late binding thus:

Sub CopyCommentsToExcel()
'Create in Word vba
Dim xlApp As Object
Dim xlWB As Object
Dim i As Integer
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err Then
        Set xlApp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
    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


The error relates to aldoDuke's addition of Function ParentLevel(Para As Word.Paragraph) As String which I have not evaluated.

The first macro (no section references) works.  I would like to use the macro with section references.  still getting the error: Run-time error '91': Object variable or with block variable not set.

 

It's a very helpful macro and thanks for all your help!

 

Kaye