Wednesday, January 31, 2007

Word macro to export comments

' This macro will export all the comments in the word doc to a new word doc.
' U can save the word doc as .txt.
' Change the extension to .csv
' Open the doc with excel and u have exported your comments to excel
Sub ExportComment()
Dim s As String
Dim cmt As Word.Comment
Dim doc As Word.Document
For Each cmt In ActiveDocument.Comments
s = s & cmt.Initial & cmt.Index & "," & cmt.Range.Text & vbCr
Next
Set doc = Documents.Add
doc.Range.Text = s
End Sub

42 comments:

  1. Anonymous6:03 AM

    Hey Jay,

    Great macro, it works. Thanks.

    ReplyDelete
  2. Anonymous6:08 AM

    Thanks from me too, Jay. That was a big help!

    ReplyDelete
  3. Anonymous3:13 AM

    Hello,
    Thank for this macro, but is it possible to extend it's functionality by exporting also marked texts in Word document, which are commented?
    In other words, I need marked texts in one Excel column and comments in second.

    ReplyDelete
  4. Anonymous4:34 AM

    to export marked texts in Word document use Comment.Scope

    ReplyDelete
  5. Anonymous6:42 AM

    Great macro, it helps me a lot. Save my time.

    ReplyDelete
  6. Anonymous1:50 PM

    Great Macro! Is there a way to also export what section, page, line# the comments is in? Thanks!

    ReplyDelete
  7. Anonymous5:33 AM

    I would like to thank you too - good job.

    ReplyDelete
  8. Anonymous6:26 AM

    You have saved me HOURS of time with this code. Thank you so much.

    ReplyDelete
  9. Awesome. Short, sweet, effective. Thanks a lot!

    ReplyDelete
  10. Anonymous7:00 AM

    How do we use this macro? Do we need to add this code somewhere to make it work?

    Regards
    V

    ReplyDelete
  11. Hey V,

    This is a code for macros in word. Find out how to create macro in word. Just google and you will find your way.

    Thanks,
    Jay

    ReplyDelete
  12. Anonymous3:19 PM

    Thanks Jay, that was very useful to export comments from word to excel.

    ReplyDelete
  13. Anonymous3:28 PM

    Exactly what I needed :-)Thanks.. Saved me a lot of time and effort. Any suggestions on how I can get Line and Page number as well..

    Thanks,

    --O

    ReplyDelete
  14. Anonymous1:46 PM

    Perfect. Thanks.

    ReplyDelete
  15. Superb macro,
    Please can u tell me how can I get comments along with the sections commented upon???
    Please help me ASAP....

    ReplyDelete
  16. Thank you very much it still works in the new Word. It worked perfectly :D

    ReplyDelete
  17. Anonymous8:15 PM

    Great macro, thanks Jay!!

    ReplyDelete
  18. Anonymous9:09 PM

    Really helpful ... thanks a lot ..

    ReplyDelete
  19. Thanks Jay that saved me a bit of time.

    I have also updated to include page number;

    ' This macro will export all the comments in the word doc to a new word doc.
    ' U can save the word doc as .txt.
    ' Change the extension to .csv
    ' Open the doc with excel and u have exported your comments to excel
    Sub ExportComment()
    Dim s As String
    Dim cmt As Word.Comment
    Dim doc As Word.Document
    'ActiveDocument.Repaginate

    For Each cmt In ActiveDocument.Comments
    cmt.Scope.Select
    iCurrentPage = Selection.Information(wdActiveEndPageNumber)
    s = s & iCurrentPage & "," & cmt.Author & " (" & cmt.Initial & ")," & cmt.Date & "," & cmt.Range.Text & vbCr
    Next
    Set doc = Documents.Add
    doc.Range.Text = s
    End Sub

    ReplyDelete
  20. This works so well. Thank you so much. Microsoft should pay you to have this feature, and shame on them for not incorporating it as a standard procedure.

    ReplyDelete
  21. Anonymous4:41 AM

    Really cool...it's just slash down my work into half ;)

    Thanks!
    Here is the version of exporting with marked text....

    Sub ExportComment()
    Dim s As String
    Dim cmt As Word.Comment
    Dim doc As Word.Document
    For Each cmt In ActiveDocument.Comments
    s = s & cmt.Initial & cmt.Index & "," & cmt.Range.Text & "," & cmt.Scope & "," & cmt.Parent & vbCr
    Next
    Set doc = Documents.Add
    doc.Range.Text = s
    End Sub

    -thirilog

    ReplyDelete
  22. Anonymous4:56 PM

    HI Jay,

    How can I include the page number? Thanks!

    ReplyDelete
  23. Anonymous5:12 AM

    Amazing macro that worked without any further editing. As others have said, it just saved me hours...and it will likely be used very heavily from now on. THANK YOU.

    ReplyDelete
  24. Anonymous6:21 AM

    Thanks Jay, worked like a charm. Now I'll sit back and relax for a bit and pretend it took me a long time to extract out all those comments :)

    ReplyDelete
  25. Hi...

    here's a version for selecting the ActiveDocument, and copying all of the comments out into a table in a new document... I have added 4 columns, and they are Comment index, date+time, the text you highlighted (scope.text) and the text of the comment (range.text)

    Sub ExportComment()
    Dim s As String
    Dim cmt As Word.Comment
    Dim doc As Word.Document
    Dim workBk As Word.Document
    Set workBk = ActiveDocument
    Set doc = Documents.Add(Visible:=True)
    Dim myRange As Range
    Set myRange = doc.Range(0, 0)
    Dim myTable As Table
    Set myTable = doc.Tables.Add(Range:=myRange, NumRows:=workBk.Comments.Count, NumColumns:=4)
    Dim i As Integer
    i = 1
    For Each cmt In workBk.Comments
    myTable.Cell(i, 1).Range.Text = cmt.Index
    myTable.Cell(i, 2).Range.Text = Format(cmt.Date, "dd/MM/yyyy HH:mm")
    myTable.Cell(i, 3).Range.Text = cmt.Scope.Text
    myTable.Cell(i, 4).Range.Text = cmt.Range.Text
    i = i + 1
    Next
    End Sub

    Best regards

    Oliver

    ReplyDelete
    Replies
    1. Rachel10:19 AM

      How would I go about changing the first column to the page number the comment appears on?

      Delete
  26. Anonymous12:21 PM

    What about adding a column for Reviewer Name and page number?

    ReplyDelete
  27. Anonymous5:38 AM

    Great, just what I needed. Thanks!

    ReplyDelete
  28. Anonymous8:36 AM

    This is great. Just what I needed for document analysis/coding!

    ReplyDelete
  29. Hi,

    this one gives you the page numbers as well. When it asks for an output file name, use something like "out.txt".

    It's inspired by the Power Point Comments Extractor provided at http://www.pptfaq.com/FAQ00900_Export_comments_to_a_text_file_-PowerPoint_2002_and_later-.htm


    Sub ExportComment()
    Dim s As String
    Dim cmt As Word.Comment
    Dim doc As Word.Document
    Dim sFilename As String

    oldpage = -1
    For Each cmt In ActiveDocument.Comments
    If (oldpage <> cmt.Scope.Information(wdActiveEndPageNumber)) Then
    s = s & vbCrLf & "Page: " & cmt.Scope.Information(wdActiveEndPageNumber) & vbCrLf
    s = s & "======================================" & vbCrLf
    End If
    ' cmt.Initial & cmt.Index & ": " &
    s = s & cmt.Range.Text & vbCrLf
    s = s & "--------------" & vbCrLf
    oldpage = cmt.Scope.Information(wdActiveEndPageNumber)
    Next

    sFilename = InputBox("Full path to output file:", "Output file")
    If Len(sFilename) > 0 Then
    WriteStringToFile sFilename, s
    SendFileToNotePad sFilename
    End If
    End Sub

    Sub WriteStringToFile(pFileName As String, pString As String)
    ' this writes the text out to a file

    Dim intFileNum As Integer

    intFileNum = FreeFile
    Open pFileName For Output As intFileNum
    Print #intFileNum, pString
    Close intFileNum

    End Sub

    Sub SendFileToNotePad(pFileName As String)
    ' This opens the file in notepad

    Dim lngReturn As Long
    lngReturn = Shell("NOTEPAD.EXE " & pFileName, vbNormalFocus)

    End Sub



    Best regards

    Thomas

    ReplyDelete
  30. Anonymous2:03 AM

    thank you for this easy way to export comments

    ReplyDelete
  31. Brilliant. Thank you!

    ReplyDelete
  32. Anonymous7:47 AM

    Thank you for the quick and easy macro folks. Appreciate the knowledge share for this.

    ReplyDelete
  33. Thanks Jay and every one else. It was a great help.
    With little bit of experimentation with the codes supplied above, I managed to put together another version of this to suit my requirements as below

    Exporting to a Table of 5 columns Page number, Reviewer Initial with comment number, Date, Commented Text and Comment in a new document

    Sub ExportComment()
    Dim s As String
    Dim cmt As Word.Comment
    Dim doc As Word.Document
    Dim workBk As Word.Document
    Set workBk = ActiveDocument
    Set doc = Documents.Add(Visible:=True)
    Dim myRange As Range
    Set myRange = doc.Range(0, 0)
    Dim myTable As Table
    Set myTable = doc.Tables.Add(Range:=myRange, NumRows:=workBk.Comments.Count, NumColumns:=5)
    Dim i As Integer
    i = 1
    For Each cmt In workBk.Comments
    cmt.Scope.Select
    iCurrentPage = Selection.Information(wdActiveEndPageNumber)
    myTable.Cell(i, 1).Range.Text =”Page” & iCurrentPage
    myTable.Cell(i, 2).Range.Text = cmt.Initial & cmt.Index
    myTable.Cell(i, 3).Range.Text = Format(cmt.Date, "dd/MM/yyyy HH:mm")
    myTable.Cell(i, 4).Range.Text = cmt.Scope.Text
    myTable.Cell(i, 5).Range.Text = cmt.Range.Text
    i = i + 1
    Next
    End Sub

    Manesh

    ReplyDelete
    Replies
    1. Anonymous7:13 PM

      Thank you very much! This was very helpful.

      Delete
  34. This is excellent.

    ReplyDelete
  35. Anonymous7:38 PM

    Great code, thanks for putting this up! I was wondering if it was possible to make this code also include the section heading number from a multi-tier heading outline?

    ReplyDelete
  36. Anonymous2:17 PM

    This code worked like a charm. It will be a fantastic help with my committee's comments on my doctoral dissertation draft.

    THANK YOU!

    ReplyDelete
  37. Anonymous7:07 AM

    A HUGE thank you to all who contributed! You have just saved my bacon!

    ReplyDelete
  38. Hello, can the full name vice the initial be added to the last macro?

    ReplyDelete
  39. Hello, very interesting post!
    Is there a way to insert the generated table at the end of the read document? in a new page?
    Thanks in advance.

    ReplyDelete