Hi all,

I'm trying to create a macro which can export comments from word to excel, so far I have this:

Sub ExportComments()
' Note: A reference to the Microsoft Excel # Object Library is required, set via Tools|References in the Word VBE.
Dim StrCmt As String, StrTmp As String, i As Long, j As Long, xlApp As Object, xlWkBk As Object
StrCmt = "Page,Line,Author,Date & Time,Comment,Reference Text"
StrCmt = Replace(StrCmt, ",", vbTab)
With ActiveDocument
' Process the Comments
For i = 1 To .Comments.Count
With .Comments(i)
StrCmt = StrCmt & vbCr & .Reference.Information(wdActiveEndAdjustedPageNumb er) & vbTab
StrCmt = StrCmt & .Reference.Information(wdFirstCharacterLineNumber) & vbTab & .Author & vbTab
StrCmt = StrCmt & .Date & vbTab & Replace(Replace(.Range.Text, vbTab, "<TAB>"), vbCr, "<P>")
StrCmt = StrCmt & vbTab & Replace(Replace(.Scope.Text, vbTab, "<TAB>"), vbCr, "<P>")


StrCmt = StrCmt & vbTab & IIf(.Parent Is Nothing, "Not a Parent", "Is a Parent")
StrCmt = StrCmt & vbTab & IIf(.Done, "Resolved", "Not Resolved")

End With
Next
End With
' Test whether Excel is already running.
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
'Start Excel if it isn't running
If xlApp Is Nothing Then
Set xlApp = CreateObject("Excel.Application")
If xlApp Is Nothing Then
MsgBox "Can't start Excel.", vbExclamation
Exit Sub
End If
End If
On Error GoTo 0
With xlApp
Set xlWkBk = .Workbooks.Add
' Update the workbook.
With xlWkBk.Worksheets(1)
For i = 0 To UBound(Split(StrCmt, vbCr))
StrTmp = Split(StrCmt, vbCr)(i)
For j = 0 To UBound(Split(StrTmp, vbTab))
.Cells(i + 1, j + 1).Value = Split(StrTmp, vbTab)(j)
Next
Next
.Columns("A: D").AutoFit
End With
' Tell the user we're done.
MsgBox "Workbook updates finished.", vbOKOnly
' Switch to the Excel workbook
.Visible = True
End With
' Release object memory
Set xlWkBk = Nothing: Set xlApp = Nothing
End Sub



However the (StrCmt = StrCmt & vbTab & IIf(.Parent Is Nothing, "Not a Parent", "Is a Parent") property doesn't actually show whether the comment is a parent or not (it labels all of them as parents, even the responses) so I need to replace it with StrCmt = StrCmt & vbTab & IIf(.Replies = 0, "Not a Parent", "Is a Parent"). When I tried replacing and running the macro again I get an error popping up saying "compile error argument not optional". I'm not familiar with coding language so would anyone be able to help me figure out how to fix this? thank you!

You need

I think your logic is flawed. Having no replies doesn't mean the comment is a child-level comment. Instead you should be looking for presence of an Ancestor - top level comments don't have an ancestor. Then you can loop through any replies if they exist. Here is a snippet of code I'm currently using...

__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia

perfect, thank you!

I think the other code provided works for now, but if I use this one, where in the code do I need to place it? thank you

You would replace
with:

Yes your right, I only just noticed when I used the other code it classed parents comments as not parents because they did not have any replies, I'll try changing it

Hi, I tried replacing this but it still classes the parent comment as not a parent? :/

where in my code do I place this, I tried copy and pasting into code but I get error messages