export word review comments in excel

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

Fantastic! Just what I was looking for and works perfectly for me. Thank you!

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

Fix for "error: Run-time error '91': Object variable or with block variable not set."

just use the Do Until loop instead of Do While loop so that one iteration is reduced. It will look like -

The Function will look like -

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 = ParaAbove.Range.ParagraphStyle

sStyle = Left(sStyle, 4)

If sStyle = "Head" Then

GoTo Skip

End If

Do Until 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

I don't think the fix above from ShardulKulkarni works, for me it changed the behaviour so that  instead of getting a paragraph reference (e.g. 2.3.4 The Title), you get the whole text of the paragraph.

I got this Error 91 when there was a comment on an object (like the title field) at the beginning of the document. I fixed it with the addition of an exit clause to the loop, that checks for a null previous paragraph. You end up with a reference of "General", which you could change if you want.

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

         If (ParaAbove Is Nothing) Then

             ParentLevel = "General"

             Exit Function

         End If

     Loop

 Skip:

     strTitle = ParaAbove.Range.Text

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

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

 End Function