[AccessD] Forcing MS Excel workbook to get its formulas recalculated and charts redrawn using Automation...

Hi Samil

In Excel VBA you need to use the following.  You might need to set the excel application first in Access VBA, depending on how you are calling Excel. If you are using an Excel Template, you can just put then xlCalcManual code in the workbook open event instead.

1:  Application.Calculation = xlCalculationManual

4: Application.Calculate or Application.CalculateFull

6: Application.Calculation = xlCalculationAutomatic

Now you can either call them from Access VBA or for Excel VBA, depending on how you are controlling XL.

Cheers
Darryl.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil Salakhetdinov
Sent: Tuesday, 8 November 2011 11:09 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Forcing MS Excel workbook to get its formulas recalculated and charts redrawn using Automation...

Hi All --

Somehow I can't get the subj working properly.
How do you make MS Excel workbook recalculation/charts redrawn implemented there using VBA Automation without SendKeys - could you please post a VBA code snippet?

I would like to 

1) set MS Excel option of Automatic formulas recalculation off on MS Excel start-up, then;
2) open existing workbook with formulas and charts;
3) get backend db data retrieved into one or more worksheets, which cells are referred in formulas and charts;
4) run recalculation of whole workbook;
5) save workbook
6) set Automatic recalculation option back to its default 'Auto' value...

I d not know how to make (1),(6) and(4) working via Automation.

Please advise.

Thank you.

--
Shamil
 

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com