Salakhetdinov Shamil
mcp2004 at mail.ru
Wed Nov 9 03:29:06 CST 2011
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 > >