Forcing calculation through automation
 

I have the following code:

xlSheet.Range['AJ1', EmptyParam].Value:='Days Lost';

xlSheet.Range['AJ2',EmptyParam].Formula:='=NETWORKDAYS(AH2,AI2,BankHols!$A$2:$A$5 00)';

xlSheet.Range['AJ2',EmptyParam].AutoFill(xlSheet.Range['AJ2:AJ'+IntToStr(kount),EmptyParam],xlFillCopy);

The third line above is very quick to execute but Excel then takes a
long time to do the calculation. How can I make sure that the entire
worksheet has been calculated before I move onto the next step?

Nirmal

Forcing calculation through automation
 

Couldn't you use Application.Calculate to do this?


"Nirmal Singh" wrote:


I have the following code:

xlSheet.Range['AJ1', EmptyParam].Value:='Days Lost';

xlSheet.Range['AJ2',EmptyParam].Formula:='=NETWORKDAYS(AH2,AI2,BankHols!$A$2:$A$5 00)';

xlSheet.Range['AJ2',EmptyParam].AutoFill(xlSheet.Range['AJ2:AJ'+IntToStr(kount),EmptyParam],xlFillCopy);

The third line above is very quick to execute but Excel then takes a
long time to do the calculation. How can I make sure that the entire
worksheet has been calculated before I move onto the next step?

Nirmal

Forcing calculation through automation
 

On Fri, 12 May 2006 12:36:01 -0700, dmthornton wrote:

Couldn't you use Application.Calculate to do this?


That doesn't seem to work either. I've tried to use this code:

xlSheet.Range['AJ1', EmptyParam].Value:='Days Lost';
xlSheet.Range['AJ2',EmptyParam].Formula:='=NETWORKDAYS(AH2,AI2,BankHols!$A$2:$A$5 00)';
xlSheet.Range['AJ2',EmptyParam].AutoFill(xlSheet.Range['AJ2:AJ'+IntToStr(kount),EmptyParam],xlFillCopy);
xlApp.Calculate;

xlSheet.Range['AJ2:AJ'+IntToStr(kount),EmptyParam].SpecialCells(xlFormulas,xlErrors).Value:=0;
xlSheet.Columns.Item['AJ:AJ', EmptyParam].Copy;
xlSheet.Columns.Item['AJ:AJ', EmptyParam].PasteSpecial(xlValues,xlPasteSpecialOperationNone , False, False);

This should calculate the NetWorkDays in column AJ, do a full calcualation and then copy and paste the values
into column AJ. However, when it has finished column AJ contains all zeros.


Nirmal