Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-10-2020, 07:21 PM
Peterson Peterson is offline Speed up macro that calls Excel-based function Windows 10 Speed up macro that calls Excel-based function Office 2019
Competent Performer
Speed up macro that calls Excel-based function
 
Join Date: Jan 2017
Posts: 141
Peterson is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 08-10-2020, 09:54 PM
Guessed's Avatar
Guessed Guessed is offline Speed up macro that calls Excel-based function Windows 10 Speed up macro that calls Excel-based function Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
Reply With Quote
  #3  
Old 08-11-2020, 06:18 AM
Peterson Peterson is offline Speed up macro that calls Excel-based function Windows 10 Speed up macro that calls Excel-based function Office 2019
Competent Performer
Speed up macro that calls Excel-based function
 
Join Date: Jan 2017
Posts: 141
Peterson is on a distinguished road
Default

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.
Reply With Quote
Reply

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
Speed up macro that calls Excel-based function Need to improve speed of Excel macro that searches for whole word matches Marrick13 Excel Programming 20 02-19-2016 09:54 AM
Speed up macro that calls Excel-based function how to speed up macro AC PORTA VIA Excel 3 10-30-2015 08:58 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:26 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft