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

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
> 




More information about the AccessD mailing list