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.

 

[Copkay123]

Paul

Thanks and apologies I should have been more clear in my post. I have no clue about how VBA Works, and would appreciate if I could be advised on the updated code.

I must add the code was magical.

Regards

 

[Macropod]

For example:

Sub ExportComments()
Dim StrCmt As String, StrTmp As String, i As Long, j As Long, xlApp As Object, xlWkBk As Object, Rng As Range
StrCmt = "Page,Line,Author,Date & Time,Comment,Reference Text,Heading #,Heading 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(.Scope.Text, vbTab, "<TAB>"), vbCr, "<P>")
      Set Rng = .Scope
      Rng.Start = .Parent.Range.Start
      With Rng
        With .Find
          .ClearFormatting
          .Text = ""
          .Style = wdStyleHeading2
          .Forward = False
          .Wrap = wdFindStop
          .Execute
        End With
        If .Find.Found = True Then
          StrCmt = StrCmt & vbTab & .ListFormat.ListString
          StrCmt = StrCmt & vbTab & .Text
        End If
      End With
    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
    .UsedRange.Columns.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