Macro “ExtractComments” –
Export comments from MS Word to MS Excel

Uncategorized

Introduction

The development of clinical documentation in drug development requires interdisciplinary communication and collaboration regarding complex, comprehensive text documents over multiple revision cycles resulting in documents of 50 to 200 pages with several hundreds of comments and replies.

Reviewing such documents is time-consuming because it is often not clear:

  • Who has added new comments?
  • Which new comments have been added?
  • Which comments are still open, ie not resolved?

To answer these questions, text-related comments are exported from a MS Word document and imported into MS Excel in a structured table, which allows the sorting and filtering of comments according to relevant attributes (see MS Excel > Data > Autofilter).

An example illustrating the basic functionality is shown in Figure 1 and 2.

Figure 1: An example of a document in MS Word with comments.

Figure 2: An example of a table in MS Excel which contains the comments in a structured format.

Attributes of a comment are represented as columns in Excel sheet:

  • Page: Page number of the comment in the original MS Word document
  • Line: Line number of the comment in the original MS Word document
  • ParentID: Identifier of the “parent comment” (initial comment) of a “child comment” (reply). The value will be “Parent” for the initial comments, and the parent identifier of child comments (replies). Replies to the same Parent comment will have the same ParentId.
  • CommentID: Identifier of a comment, ie an initial comment or reply to a comment
  • Author: Author of a comment
  • Date: Date of comment (YYYY-MM-DD, as text)
  • Time: Time of comment (hh:mm, as text)
  • Status: Status of comment as “open” or “resolved”
  • Comment: The content of the comment
  • TargetText: The targeted, marked text which the comment relates to
  • File: The file name of the MS Word document
  • Path: The full path of the MS Word document

Requirements

The Macro was tested on the following system:

  • MS Word 365
  • MS Excel 365
  • Microsoft Visual Basic For Applications (MS VBA, Version 7.1, already integrated in MS Office Suite)
  • The ExtractComments Macro source code

Other environments may also work.

Enable VBA (Macro) Functionality

Activate Macros in MS Word

MS Word > File > Options > Trust Center > Trust Center Settings > Macro Settings > “Enable all macros”

Note: This can allow potentially dangerous code to be run. The host application (eg, MS WORD here) may need to be restarted for changes to become effective.

View Macros in MS Word

MS Word > View > Macros > Macros > View Macros

Create Macros in MS Word

MS Word > View > Macros > Macros > View Macros > Enter new Macro Name “ExtractCommentsMacro” > Create > Enter code in VBA editor window

Note: You can start the VBA editor with the shortcut ALT+F11 in MS Office applications.

Add Macro Menu to Menu Ribbon in MS Word

MS Word > Right-click Menu Ribbon > Customize the Ribbon … > Customize the Ribbon: > New Tab > Activate “New Tab”

 … > Rename > Enter New Ribbon Tab Name “MyTools”

… > Rename > Enter New Ribbon Group Name “ReviewTools”

Add Macro Button to Menu Ribbon in MS Word

MS Word > Right-click Menu Ribbon > Customize the Ribbon … > Customize the Ribbon > Choose Commands from: > Macros > Select Macro, e.g. “Normal.NewMacros.ExtractComments” > Use “Add >>” to move to target Ribbon Tab and Ribbon Group

Note: You can use the “Rename” Button to the rename the Macro Button to “ExtractCommentsButton”

Activation of “Microsoft Excel 16.0 Object Library”

MS WORD > ALT +F11 > MS VBA Editor > Tools > References > Available References: > Scroll down and tick (activate) “Microsoft Excel 16.0 Object Library” > OK

Note:

  • If menu item “References” is not available (“greyed out”), you may be in “Debugging Mode”.
    To leave Debugging Mode: MS VBA Editor > Run > Reset
  • The “Microsoft Excel 16.0 Object Library” needs to be enabled in the MS Word VBA editor to be able to access MS Excel objects, e.g. Worksheets, from MS Word. If not enabled, the macro will give an error.

Add Developer Menu to Menu Ribbon in MS Word (Optional)

MS Word > Right-click Menu Ribbon > Customize the Ribbon … > Customize the Ribbon: > Tick (Activate) “Developer”

Note:

  • This will give you a menu button to start VBA editor, so you do not have to remember the shortcut ALT+F11, and other more advanced features.

Application of the Macro “ExtractComments”

  • Open Word document, e.g. .docx
  • Save as “Word Macro-Enabled Document (.docm)”
  • Execute Macro “ExtractComments”: MS Word > MyTools > ReviewTools > ExtractCommentsButton

Acknowledgements

The Macro “ExtractComments” was inspired by three open-source VBA Macros which were created for extracting MS Word comments:

Appendix: Listing of the ExtractComments() Macro

Current version is shown below. Code history is available in BitBucket repository [Link].

' VBA Macro "ExtractComments" to export comments from MS Word Document and import to MS Excel
' Author: Wilmar Igl, PhD
' Date: 2023-08-11
' Link: https://wilmarigl.de/?p=781
'
' This program comes with ABSOLUTELY NO WARRANTY;
' This is free software, and you are welcome to redistribute it under certain conditions
' as described in the GNU GPL 3.0 (GNU General Public License, Version 3.0).
' Link: https://www.gnu.org/licenses/gpl-3.0.en.html

Sub ExtractComments()

'Set references to the "Microsoft Excel 16.0 Object Library"
Dim xApp As Excel.Application
Dim xWb As Excel.Workbook
Dim i As Integer

Set xApp = CreateObject("Excel.Application")
xApp.Visible = True
Set xWb = xApp.Workbooks.Add ' create a new workbook

'Insert table headings
With xWb.Worksheets(1)
.Cells(1, 1).Formula = "Page"
.Cells(1, 2).Formula = "Line"
.Cells(1, 3).Formula = "ParentID"
.Cells(1, 4).Formula = "CommentID"
.Cells(1, 5).Formula = "Author"
.Cells(1, 6).Formula = "Date"
.Cells(1, 7).Formula = "Time"
.Cells(1, 8).Formula = "Status"
.Cells(1, 9).Formula = "Comment"
.Cells(1, 10).Formula = "TargetText"
.Cells(1, 11).Formula = "File"
.Cells(1, 12).Formula = "Path"
End With

'Insert values of comment attributes
With xWb.Worksheets(1)

For i = 1 To ActiveDocument.Comments.Count

.Cells(i + 1, 1).Formula = ActiveDocument.Comments(i).Reference.Information(wdActiveEndAdjustedPageNumber)
.Cells(i + 1, 2).Formula = ActiveDocument.Comments(i).Reference.Information(wdFirstCharacterLineNumber)

If (ActiveDocument.Comments(i).Ancestor Is Nothing) Then
.Cells(i + 1, 3).Formula = "Parent"
Else
.Cells(i + 1, 3).Formula = ActiveDocument.Comments(i).Ancestor.Index
End If

.Cells(i + 1, 4).Formula = ActiveDocument.Comments(i).Index
.Cells(i + 1, 5).Formula = ActiveDocument.Comments(i).Contact
.Cells(i + 1, 6).Formula = Format(ActiveDocument.Comments(i).Date, "'yyyy-mm-dd")
.Cells(i + 1, 7).Formula = Format(ActiveDocument.Comments(i).Date, "'hh:mm")
.Cells(i + 1, 8).Formula = CStr(ActiveDocument.Comments(i).Done)
.Cells(i + 1, 9).Formula = ActiveDocument.Comments(i).Range
.Cells(i + 1, 10).Formula = ActiveDocument.Comments(i).Scope
.Cells(i + 1, 11).Formula = ActiveDocument.Name
.Cells(i + 1, 12).Formula = ActiveDocument.FullName
Next i
End With

'Replace values in Status variable
Dim oRange As Object
Set oRange = xApp.ActiveSheet.Range("H:H")
With xWb.Worksheets(1)
    oRange.Replace What:="TRUE", Replacement:="Resolved", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

    oRange.Replace What:="FALSE", Replacement:="Open", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End With

'Format table
xWb.Worksheets(1).Rows(1).Font.Bold = True
xWb.Worksheets(1).Rows().HorizontalAlignment = xlLeft
xWb.Worksheets(1).Columns().AutoFit
xWb.Worksheets(1).Columns("I").ColumnWidth = 32
xWb.Worksheets(1).Columns("J").ColumnWidth = 32

'Sort table
xWb.Worksheets(1).Sort.SortFields.Clear
xWb.Worksheets(1).Range("A:K").Sort _
  Key1:=Range("A1"), Order1:=xlAscending, _
  Key2:=Range("B1"), Order2:=xlAscending, _
  Header:=xlYes
xWb.Worksheets(1).Sort.SortFields.Clear

Set xWb = Nothing
Set xApp = Nothing
End Sub