I've cobbled together a macro that loops through all of the comments in a file and modifies each with text strings from an Excel file, which the macro obtains via a function I found on the Web.
The macro is running very slowly, presumably because the function is opening/closing Excel each time it obtains another string?? But when I mess with the function, I either get errors, or I end up with the Excel file being locked for editing after running the macro.
Can you offer some guidance on the best way to approach this?
Code:
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