Salakhetdinov Shamil
mcp2004 at mail.ru
Fri Nov 11 15:51:24 CST 2011
Hi Darryl -- I have solved the issue - it was caused by improper OpenXML SDK 2.0 calls, when fixed C# -> MS Excel automated workbook recalculation started to work OK. Thank you for your help. -- Shamil 09 ноября 2011, 13:31 от Salakhetdinov Shamil <mcp2004 at mail.ru>: > Hi Darryl -- > > Thank you for your reply. > I' m using MS Excel 2010. > I'm working on C# Excel Automation custom solution (in principle/on conceptual COM technology level there is no any difference in coding C# or VBA MS Excel Automation). > The task is to have MS Excel workbook opened, run calculations and saved. > Easy of course. But workbook recalculations and charts redraw don't work properly/at all. > I must note I do use OpenXML SDK 2.0 (http://www.microsoft.com/download/en/details.aspx?id=5124) to fill workbook's data in "Excel Automation free" mode - OpenXML SDK works with MS Excel workbooks on file/workbook's XML package level providing high level operations to manipulate worksheets cells' data, charts etc. > After workbook's data is filled using open XML SDK I do use Automation to open workbook and to run calculations and charts redraw (Open XML SDK doesn't have functionality to run Excel calculations) - and that calculation and redraw do not work (well). > It could be side-effect of using OpenXML SDK to fill workbook's data. > I will work more on this issue and I will keep posting on my progress. > It should be easy task, just missing something I guess... > > Opening MS Excel workbook using Automation, setting manual recalculation mode and related options are needed mainly for testing. > > Thank you. > > -- Shamil > > 09 ноября 2011, 03:19 от Darryl Collins <darryl at whittleconsulting.com.au>: > > 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 > > > > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >