Shamil Salakhetdinov
shamil at smsconsulting.spb.ru
Tue Nov 8 06:16:09 CST 2011
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