Darryl Collins
darryl at whittleconsulting.com.au
Tue Nov 8 17:17:35 CST 2011
Hi Shamil.
If Excel is in automatic mode when the workbook is opened the calculation will take place before any VBA event fires, so you cannot prevent it.
You can switch to manual calc in the workbook_open event but this happens after the workbook is open.
If the problem is caused by opening a workbook in a later version of excel than the version that last calculated it, you can apply a registry fix.
Note that Excel sets the calculation mode from the first workbook opened in a session: when you open two workbooks, one saved in manual mode and one saved in automatic mode, they will both have the calculation mode of the first workbook opened.
So based on this there may be a kludge type workaround. That is to use code to open a blank workbook and set the calculation mode to manual, and then open your 2nd workbook. This should prevent the second workbook for auto calcing on open. I have not tested this, but it should work based on the info above.
You can then set calc to manual in your 'real' workbook as part of the open event using
Private Sub Workbook_Open()
Application.Calculation = xlCalculationManual
End Sub
I will keep poking around. Can you tell me which version of XL you will be using for this?
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:16 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Forcing MS Excel workbook to get itsformulas recalculated and charts redrawn using Automation...
Hi Darryl --
Thank you for your reply.
MS Excel has also a "sub-option" for 'Manual' recalculation mode:
"Recalculate Workbook before saving".
I would like it to be switched off by VBA code - and I can't find how.
Also even with 'Manual' recalculation mode set manually, and "Recalculate Workbook before saving" set manually to false, when workbook is closed and reopened it gets all its formulas recalculated and charts redrawn - and I wanted to suppress that "automatism" completely. Do know how to do that?
Thank you.
--
Shamil
P.S. I could be out of this forum for a couple of days...
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins
Sent: 8 ?????? 2011 ?. 3:22
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Forcing MS Excel workbook to get itsformulas 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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com