stephenlecomptejr
asked on
Adding formula to range is taking too long using Access VBA syntax.
Please note the following code. At about the line: For j = 2 To lLastRow I start to change the formulas for rows 2- 2000 in under column AR. Please note I need to have the formula sum of AI through AP for each row under AR. So I build this formula using for / each. The problem is that it takes a little long to have the formula update for 2,000 rows - it can take 2-3 minutes to run. Is there another syntax I can try that will update the range quicker?
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim sFormula As String
Dim rs As DAO.Recordset
Dim j As Long
Dim lLastRow As Long
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(sFile)
Set xlSheet = xlBook.Worksheets(1)
xlSheet.Activate
xlSheet.Application.Visible = False
Set rs = Application.CurrentProject.Application.CurrentDb.OpenRecordset(sSQLExcel)
With xlSheet
.Range(sRange).copyfromrecordset rs
.Range("AR:AZ").NumberFormat = "$#,##0.00;$-#,##0.00;"""""
lLastRow = 2000
For j = 2 To lLastRow
sFormula = "=SUM(AI" & j & ":AP" & j & ")"
sFormula = Replace(sFormula, " ", "")
.Range("AR" & j).Formula = sFormula
Next j
End With
rs.Close
Set rs = Nothing
Set xlSheet = Nothing
xlBook.Save
xlBook.Close
Set xlBook = Nothing
Set xlApp = Nothing
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok. So I misspoke. I just changed the one line of code that didn't implement late binding to this:
And I just added xlSheet. in front of the application code.
Thank you sincerely for your response!
And I just added xlSheet. in front of the application code.
xlSheet.Application.ScreenUpdating = False
xlSheet.Application.DisplayStatusBar = False
xlSheet.Application.Calculation = -4135
xlSheet.Application.ScreenUpdating = True
xlSheet.Application.DisplayStatusBar = True
xlSheet.Application.Calculation = -4105
Thank you sincerely for your response!
ASKER
I've requested that this question be closed as follows:
Accepted answer: 500 points for samo4fun's comment #a40400680
Assisted answer: 0 points for stephenlecomptejr's comment #a40401040
for the following reason:
Just needed a tweak on the code but the idea worked well. Thank you sincerely for replying!
Accepted answer: 500 points for samo4fun's comment #a40400680
Assisted answer: 0 points for stephenlecomptejr's comment #a40401040
for the following reason:
Just needed a tweak on the code but the idea worked well. Thank you sincerely for replying!
ASKER
Works great - need to give full points to this person's reply. I appreciate it.
ASKER
For example the debug dies at Application.ScreenUpdating