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

Masarah

New Member
Joined
Jan 11, 2020
Messages
1
Office Version
  1. 2019
Platform
  1. MacOS
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.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Try:
VBA Code:
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.
 
Upvote 0
The 'Note' in the above code is incorrect - as late binding is used, there is no need to set a reference to Excel.
 
Upvote 0
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"?
 
Upvote 0
You could, for example, add:
Code:
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
 
Upvote 0
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!
 
Upvote 0
I have the same problem with the blank reference text column. May I ask for a more detailed help on this matter.
Thanks!
 
Upvote 0
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?
 
Upvote 0
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!

 
Upvote 0

Forum statistics

Threads
1,223,900
Messages
6,175,276
Members
452,629
Latest member
SahilPolekar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top