Retrieving Data Out of SQL into EXCEL - Pausing formulas | VBA

ssv123

New Member
Joined
Nov 16, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have created a template that retrieves data out of SQL into Excel. On another worksheet, formulas are automatically referencing the data being retrieved in real time, which greatly impedes retrieval speed as it is calculating during the retrieval.

When selecting the Manual Calculation option, the retrieval performs extremely fast, but then requires the user to select the Calculate Now button. Ideally, this entire process is hands off without any user intervention, other than the initial retrieval.

Could someone please point me in the right direction regarding any VBA that would trigger the 'Calculate Now' button upon the completion of the data being fully retrieved and dumped into Excel?

Thank you,
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

ekrause

New Member
Joined
Aug 7, 2019
Messages
49
You can use these in your VBA code to set the calc rules of the workbook. Application.Calculate would be the same as hitting "Calculate Now" in VBA, when you switch from xlCalculationManual to xlCalculationAutomatic it should calc automatically *citation needed*. Assuming your SQL retrieve is being initiated via VBA you can add the Application.Calculate immediately afterward.

VBA Code:
Application.Calculation = xlCalculationAutomatic
Application.Calculation = xlCalculationManual
Application.Calculate
 

Watch MrExcel Video

Forum statistics

Threads
1,129,489
Messages
5,636,626
Members
416,931
Latest member
pattichis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top