#1
|
|||
|
|||
Speed up macro that calls Excel-based function
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 |
#2
|
||||
|
||||
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
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#3
|
|||
|
|||
Thank you very much for modifying (and neatening!) the code, Andrew! After I posted last night, I started messing with arrays and was able to get as far as passing one from the function back to the macro, but you've gotten me all the way there.
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Concurrent Calls in Excel | sbcccc | Excel | 1 | 08-15-2018 12:24 AM |
Is there a way to search all workbooks for speciic function calls? | Jennifer Murphy | Excel Programming | 4 | 07-01-2018 03:55 AM |
Rest api calls in excel | Santhosh_84 | Excel Programming | 0 | 06-24-2016 10:31 AM |
Need to improve speed of Excel macro that searches for whole word matches | Marrick13 | Excel Programming | 20 | 02-19-2016 09:54 AM |
how to speed up macro | AC PORTA VIA | Excel | 3 | 10-30-2015 08:58 PM |