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



## Masarah (Jan 11, 2020)

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 (Jan 17, 2020)

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 (May 12, 2021)

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


----------



## blacksky200 (Dec 20, 2021)

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 (Dec 20, 2021)

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 (Jan 5, 2022)

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 (Jan 9, 2022)

That suggests you have empty paragraphs using that heading Style


----------



## fpr (Feb 22, 2022)

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


----------



## mtneal (Feb 22, 2022)

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 (Feb 23, 2022)

mtneal said:


> 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


----------



## Masarah (Jan 11, 2020)

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.


----------



## magg0t (May 23, 2022)

Thank you so much for this great VBA code ! it work like a charm for me !!! 
I have a FU question though, do you know if there is a code to add to have a column showing if comment is marked as "resolved" or not ? This way I can also filter this on the excel export.

Thank you !!


----------



## Macropod (May 23, 2022)

Surely it's a simple matter to add a suitable column heading to the workbook after the macro has finished exporting the data??? Besides which, from where would the macro get the 'resolved' data?


----------



## magg0t (May 23, 2022)

Macropod said:


> Surely it's a simple matter to add a suitable column heading to the workbook after the macro has finished exporting the data??? Besides which, from where would the macro get the 'resolved' data?


Thanks for your reply Paul !  
I have more than 600 comments in my file so adding this column manually after is a lot of work 😰
I'm a total newbie in VBA so I was asking a naive question, and was simply asking if such a code was possible to add in the VBA code the same ways as the page number, the date, etc.


----------



## Macropod (May 23, 2022)

Manually adding an extra column heading in Excel afterwards takes perhaps 5 seconds...

And you still haven't answered the second question.


----------



## Dorudon (Jun 20, 2022)

This would be a total lifesaver for me if it worked. However, when I run it I get "Compile Error: Invalid Outside Procedure". Im not knowledgeable on this sort of thing so if someone can tell me how to fix this it would be a Godsend. Thank-you


----------



## Macropod (Jun 20, 2022)

Since the code works as posted, once can only assume there is a problem with how you've tried to implement it. I suspect you've omitted the first line:
Sub ExportComments().

All you need to do is to copy and paste the entire code and paste it into any code module in the document you want to run it from.


----------



## Dorudon (Jun 24, 2022)

AceNS said:


> 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!
> ...


Apologies my fault entirely. I didnt notice the scroll bar so the whole thing was not copied and pased


----------



## Macropod (Aug 31, 2022)

galo said:


> 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!


Coding error - replace .Reference.Text with .Scope.Text


----------



## Copkay123 (Sep 14, 2022)

Macropod said:


> You could, for example, add:
> 
> ```
> StrCmt = StrCmt & vbTab & .Scope.GoTo(What:=wdGoToBookmark, Name:="\HeadingLevel").Paragraphs.First.Range.ListFormat.ListString
> ...


What modifications do i need to do to be able to extract the Headling Level 2


----------



## Macropod (Sep 14, 2022)

You have two options:
1. implement a loop that keeps looking for a previous heading at the heading 2 level; or
2. Use Find to locate a previous heading at the heading 2 level.


----------



## Masarah (Jan 11, 2020)

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 (Sep 15, 2022)

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 (Sep 15, 2022)

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
```


----------

