Sub Comments_AddResponsesToAll_6() ' 08/10/2020
' This macro loops through all comments in a file and adds text,
' sourced from an Excel file, to the beginning of the comment. 

    Dim rngNewComment As Range
    Dim rngComment As Range
    
    Dim lngCommentNum As Long
    Dim lngExcelID_RowNum As Long
      
    ' Initially set the Excel ID row number; these numbers do NOT correlate
    ' with the comment numbers in the Word files:
    lngExcelID_RowNum = 2
      
    For lngCommentNum = 1 To (ActiveDocument.Comments.Count)
                     
        Set rngComment = ActiveDocument.Comments(lngCommentNum).Range
        
        rngComment.Collapse Direction:=wdCollapseStart
        
        rngComment.Text = Read_Excel_Cell(lngExcelID_RowNum) & vbCr
                
        'Increment the Excel row number:
        lngExcelID_RowNum = lngExcelID_RowNum + 1
    Next
  
End Sub

Function Read_Excel_Cell(cellRin As Long) As String
' https://stackoverflow.com/questions/47486565/insert-text-from-an-excel-file-into-a-word-document-word-vba

    Dim objExcel As Excel.Application
    Dim myWB As Excel.Workbook

    Set objExcel = New Excel.Application
    Set myWB = objExcel.Workbooks.Open("C:\Temp\TestFile.xlsx")
    ' Below, the first number here -- ".Cells(cellRin, 15)" -- is the row number, the second is the column number:
    Read_Excel_Cell = myWB.Sheets(1).Cells(cellRin, 15)
    myWB.Close
    Set myWB = Nothing
    Set objExcel = Nothing

End Function

Sub Comments_AddResponsesToAll_6() ' 08/10/2020
  ' Loops through all comments and appends text from Excel to beginning of the comment.
  Dim rngComment As Range, lngCommentNum As Long, arrExcelData As Variant
  
  arrExcelData = GetExcelData   'get the excel data once and store in an array for reuse
   
  For lngCommentNum = 1 To (ActiveDocument.Comments.Count)
    Set rngComment = ActiveDocument.Comments(lngCommentNum).Range
    rngComment.InsertBefore arrExcelData(lngCommentNum, 14) & vbCr
  Next
  
End Sub

Function GetExcelData() As Variant
    Dim myWB As Excel.Workbook
    Dim objExcel As New Excel.Application
    Set myWB = objExcel.Workbooks.Open("C:\Temp\TestFile.xlsx")
    GetExcelData = myWB.sheets(1).UsedRange.Value
    myWB.Close
    Set myWB = Nothing
    Set objExcel = Nothing
End Function