1

I am running below one VBA code and in that CopyFromRecordset is being called multiple time and taking too much time for execution.

Is there any way to reduce execution time?

DBPath = ThisWorkbook.FullName
Sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"
con.Open Sconnect
SQLQuery = "Select * from [ALM_Scenario_Dump$] where Scenario_Name='" & Scenario_Na & "'"
mrs.Open SQLQuery, con
               ThisWorkbook.Sheets("ALM_Scenario_Detailed_Temp").Range("A2").CopyFromRecordset mrs
mrs.Close
con.Close
braX
  • 11,506
  • 5
  • 20
  • 33
Pavan Shriram Jangid
  • 23
  • 7

1 Answers1

0

If you don't have already, you can add these before copying over the recordset:

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlManual

Then turn them back to normal after the data is copied over.

The only other way I can think of for copying over the recordset data would be to loop through all of the fields in the recordset but I can't imagine this being any faster.

Please also see SwiftJr's answer on: VBA: Querying Access with Excel. Why so slow?

Maybe you can set your cursor location with CursorLocation = adUseClient on your connection as the answer notes

Kubie
  • 1,551
  • 3
  • 12
  • 23