Microsoft Office Forums - View Single Post - [Solved] Speed up macro that calls Excel-based function

Guessed · 08-10-2020, 09:54 PM

You are only reading from Excel so you don't need it open once you read the info from it. Read the sheet in as an array (or just the 15th column of cells for efficiency) and then have your loop get the values from that array.

Code:

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