Export Comments with referred text and line numbers from Word to Excel

[Masarah]

Hello,

Unfortunately, I have no idea about Macros and VBA and everything I find in the internet is half information so I hope someone can tell me step by step what to do:

For my master thesis, I commented on interviews in Word.
To analyse further, I need to export the comments with the referred text and the line number (from word doc) to an excel spread sheet - so 3 columns (line - comment - referred text) with one line per comment.

1) How do I start the process in word? I have word 2019. Under "View", there is a "Macros" option, but it only says "record" and "view", but I can not record
2) Which code do I have to type in (as far as I understood from my internet research, i need to enter a code)
3) Is there something more I have to do or does the excel open and create itself?

Thank you yo much for your help!

Best
S.

 

[Macropod]

Try:

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(wdActiveEndAdjustedPageNumber) & 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(.Reference.Text, vbTab, "<TAB>"), vbCr, "<P>")
    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

Note: Although Word can return the line # on a given page, it can't easily do so for the line # in the document as a whole.

 

[Macropod]

The 'Note' in the above code is incorrect - as late binding is used, there is no need to set a reference to Excel.

 

[blacksky200]

Sorry if I am reviving this old thread.

Is there a way to extract the section (number and heading) that the commented text appears in?
So if I have commented in the introduction, is there a way to extract (for example) "1. Introduction"?

 

[Macropod]

You could, for example, add:

StrCmt = StrCmt & vbTab & .Scope.GoTo(What:=wdGoToBookmark, Name:="\HeadingLevel").Paragraphs.First.Range.ListFormat.ListString
StrCmt = StrCmt & vbTab & .Scope.GoTo(What:=wdGoToBookmark, Name:="\HeadingLevel").Paragraphs.First.Range.Text

 

[galo]

Hi all,
for some reason, the reference text column remains empty when running the code (all other columns include the right data)- any suggestions?
Many thanks!

 

[Macropod]

That suggests you have empty paragraphs using that heading Style

 

[fpr]

I have the same problem with the blank reference text column. May I ask for a more detailed help on this matter.
Thanks!

 

[mtneal]

I'm trying to use this VBA to export comments from a 365 Word Doc to an Excel sheet on MacOS, but hit a 438 error that it doesn't support the property or method on this line " Set xlWkBk = .Workbooks.Add"

Does anyone have any ideas or recommendations?

 

[AceNS]

I'm trying to use this VBA to export comments from a 365 Word Doc to an Excel sheet on MacOS, but hit a 438 error that it doesn't support the property or method on this line " Set xlWkBk = .Workbooks.Add"

Does anyone have any ideas or recommendations?

Hi there,
Im not sure how you can make it work on Excel but you can use this link that will do the same thing but on Microsoft Word. I just tried it- it added line#, page #, author, where comment is coming from, comment, author and time comments was made.

Hope this is helpful!

Add-Ins for Microsoft Word – Improve Productivity in Word

DocTools Word Add-Ins - productivity tools for Microsoft Word. Work more efficiently in Word with powerful, easy-to-learn, easy-to-use add-ins from DocTools.

wordaddins.com