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

[ssv123]

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,

 

[ekrause]

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.

Application.Calculation = xlCalculationAutomatic
Application.Calculation = xlCalculationManual
Application.Calculate