export word review comments in excel

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'

I have used this Macro before, and it's been really helpful! But now it won't work?

Thanks in advance

Simon

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.